This is what your spreadsheet should look like apart from the two values input at A3 and B3

The reason it looks so bare is that, firstly we have not put any values into it 

and secondly the formulae are keeping it blank, until the values are put in. 

By the way, did you remember to copy the formula from F4 to all the cells 

down to F20?  Use the little black box technique now to get that done before

continuing.  Note  that we do NOT want the formula in cell F3. That cell 

must be left blank.

OK, now to test it.  Put the following values in the cells marked by the cell 

in the [   ].  For example 3.4 [A10] means put 3.4 in cell A10.  

Get the idea (for those of you who have not followed this through).

26/07 [A3]

32096 [B3]

31/07 [A4]

32288 [B4]

1.19 [C4]

38.8 [D4]

Saturday [A5]

19844 [B5]

1.18 [C5]

40.9 [D5]

Have a good look at your spreadsheet and see if it matches the picture above.

You can see a normal line on row 4 (that is A4 to H4), but row 5 is 

completely messed up

The formatting does not stop the input of  'Saturday', the mileage value in B5

 has thrown everything upside down beyond E5. 

C5,D5 and E5 are OK as the values are in the accepted range. 

You can see that the formula we dealt with in the last post is doing its job 

and warning the user that the value put into B5 is too small.  G5 and H5 rely 

on the values elsewhere to be with an acceptable range, which in this 

case they clearly are not! 

We will see what can be done with this next time

That's all for now

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:

Winding up the Mileage spreadsheet part 2

Hi People


From the last post, we  were looking at the formula  below (if you cannot remember have a look at the previous blogs).  The formula was created in cell F4 (in the explanation below it is called the 'target cell').


=IF(B4<>"";IF(B4>B3;B4-B3;"value too 


small");"")


The formula below highlights the TEST1, TRUE1, FALSE1 parts of the formula by underlining the parts 


Test1


=IF(B4<>"";IF(B4>B3;B4-B3;"value too 


small");"")   




True1


=IF(B4<>"";IF(B4>B3;B4-B3;"value too 


small");"")




False1


=IF(B4<>"";IF(B4>B3;B4-B3;"value too 


small");"")  


Ok so verbally what this formula says:
TEST (underlined Test1 section)
IF the contents of cell B4 is NOT EQUAL ( '<>' is a NOT EQUAL sign) to NOTHING (remember  that two quote marks - without a space between - is NOTHING)


IF the above condition is TRUE (True1 underlined section) then calculate the second IF test (we will go througn this below)


IF the above condition is FALSE (False1 underlined section) then the contents of B4 are set at "" (nothing).


Now onto the second IF Test in the formula (remember for the second test to be calculated, the first test must be True).


Test2


=IF(B4<>"";"";IF(B4>B3;B4-B3;"value 


too small");"")




True2


=IF(B4<>"";IF(B4>B3;B4-B3;"value too 


small");"")




False2


=IF(B4<>"";IF(B4>B3;B4-B3;"value too 


small");"")


The underlined section  (Test2) above states that if the contents of cell B4 is GREATER THAN the contents of B3


If the Test2 is True then True2 will be adopted and the cell contents will contain the value left after taking the contents of B3 away from the contents of B4  (e.g.  IF B4 contains 38800 and B3 contains 38400 then the target cell - the cell which contains this formula - will end up with 38800-38400 = 400 as its contents.


If the Test2 is False then False2 will be displayed.  The cell will contain the words 'value too small' as a warning that the value in B4 is lower than that in B3.  Note here that words are enclosed in quotation marks "value too small" in the formula.


We will play with the spreadsheet in the next session.


Until then


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: