Tidying up the Spreadsheet

Hi People

In the last post we were testing the spreadsheet, when a couple of incorrect inputs threw up a " #VALUE! " error in the G5 and H5 cells.

How can we get around this?

Well one solution would be to test one of the cells which throw up the error, or you could test to see if the result of a calculation produces an exception error.

This is the approach we will adopt here, as then we cover the possibility of E4 containing nothing and F4 having a value too small ( this means a negative value being produced in F4).

So take a look at the formula below

=IF(E4="";"";IF(F4="value too small";"";E4/F4))


There are two conditional IF statements, one of which is only checked if the first IF statement is FALSE.

Remember that the basic format for an IF conditional formula is

=IF(Test;True Result;False Result)

So to explain this, the above says IF the Test is True then use the True Result, but IF the Test is False then use the False Result.

OK enough of the revision.

  Our new formula (which is put into cell G4) says:
IF the value in E4 equals Nothing ( "" ) then the True result equals Nothing ( "" ), but IF there is a value in E4 then process the second IF fornula.

Which reads as

IF the value in F4 equals "value too small" (the value has to equal the quote exactly, but as we put the value as a result to an earlier calculation, this is going to be a known result) then the True result equals Nothing ( "" ) but IF there is a value in F4 then put the True result ( in this case the  result of a calculation F4 / F4 ) into the cell.

If you put this formula into G4 and use the 'black box' method to copy the the formula, this will deal with the possible " #VALUE! " display in the G column, but can you use the above information to create a formula for the H column?


We will go through this next time


Also if there are any suggestions for another spreadsheet you would like to see developed, please leave a comment and we will go through the most popular request.


Thank You for your attention


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

eMail address:
First Name: