Hi People
Last time we created a 'nested' IF formula for the cell range G4 to G20. It solved the issue with the '#VALUE!' display if there was no value in the E4, E5 .... or if F4, F5 .... contained a "value too small" result.
Following this I requested that you have a go for yourself at creating a similar formula for the H4-H20.
Did anyone do it?
OK, Good on you if you had a go and it didn't quite work.
Keep it up and you will get there.
Brilliant, if you did and it worked, you're getting the hang of this whole spreadsheet thing.
ALWAYS test your spreadsheet for common errors at least, but if you can test it for all sorts of errors you can come up with. This will make it more robust to the user.
The formula, I came up with for cell H5, was
=IF(D5="";"";IF(F5="value too small";"";F5/D5))
[ See how similar it is to the one in G5
=IF(E5="";"";IF(F5="value too small";"";E5/F5)) ]
We are checking, first of all, that there is no value in D5, if there is no value then we display no value (nothing represented by "").
If there is a value in D5, then we check the F5 cell for "value too small", if there is a "value too small" result then nothing is displayed, if the F5 cell contains an acceptable value, then H5 will contain the result of F5/D5.
You will also notice in this formula that both the cells used in the final calculation are tested for values.
Covering your 'exceptions' like this are normal, when creating a spreadsheet, as they frequently pop up, due to the user mis-typing or not understanding what value types (or ranges - we will look at these sometime too) are supposed to be in the cells to get the calculation to work.
Now clearly you cannot cover all possible errors, but the main ones should be covered either by providing error messages (such as the "value too small" message), or a no entry, where excessive error messages may appear.
Note that too many error messages may well hide the original error, from untrained eyes.
We will look at further error handling in future, but for now that is it for this post
Thank You for your company, and see you next time
Prometheus1618
We now have a FREE SPYWARE REPORT available, yours to keep and share if you wish. Fill in your details below and get yours now
No comments:
Post a Comment