Tidying up the Spreadsheet - part 2

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

eMail address:
First Name:

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:

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:

Winding up the Mileage spreadsheet part 1

Hi People

Your  spreadsheet is now taking shape, enter the two remaining formulae as shown below, to make sure that when you copy the formulae (by the way formula is singular i.e  1 formula  2 or more are formulae) down the appropriate column (from row 3 to row 20).

For column G  we put

=if(E3="";"";E3/F3)
And using the little black box method to copy the formula to cell G3 to G20

For column H we put

=if(D3="","",F3/D3)
Again copy the formula to cells H3 to H20

There is one thing which we can do, to make things a little easier for you.  When you put in your mileage, from the start mileage you need to find out what the second mileage is and then subtract the start mileage from the second mileage.

This might be easier to explain with an example.

You decide to find out how many miles you do between filling up at the fuel station.

What you need is a full tank to start and the start mileage.  Now you cannot work out anything until you fill up with fuel again and get second mileage.  Once you have both, you can find out how many miles you have travelled and how many litres of fuel you have used.

So having put the start mileage into the spreadsheet, you cannot work out anything until the next fuel fill up and  mileage reading.  Therefore only cells A3 and B3 can have a value when you start using this spreadsheet.

On the next fill up you have your second mileage which can be used (by the spreadsheet) to calculate (yes I said calculate) the miles done.

To do this put in cell F4

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

See if you can work out what is going on here (hints:  The '<>' is a 'not equal' sign , the '>' is a 'greater than' and look for 2 conditional if calculations one inside the other)
We will go through it together next time.

Until next time Thank You


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:

Formula explanation

Hi People

The last post was a little lengthy and a number of new concepts (for a total beginner)

The formula which we left off at was the one below

= if (d3="";"";c3*d3)     remember that c3 is the same as C3 etc

OK, now remember that ALL calculations start with an '=' sign, so that part is standard with any calculation.

The ' if ' part is the most common way of setting a conditional calculation. The format is

=if (something is true; do this ; if false do this)
There are 3 parts to this calculation

1. The test - in our equation we are testing if there is anything in cell d3
2. True option - if the content of d3="" then display "" (the double quote marks with nothing - not even a space - are the same as saying nothing) so the test is saying if d3="" (nothing) then put "" (nothiing) in this cell.
3. The false option - if the content of d3=3 (clearly there is something there now) then multiply the contents of c3 by the contents of d3 and put the result in this cell.

Can you figure out what to do for the other two  calculated columns (only g3 to g20 and h3 to h20 need to be used for the moment).

That is all this time people

Thank You

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: