Personal Accounts - Conditional Formatting part 2

Hi People

The formula I left you with last time was quite large wasn't it.  Let's have another look at it.

=IF(E7="";"";IF(OR(D7="C";D7="c");E7+F6;IF(OR(D7="D";D7="d");F6-E7;"WHAT!")))

This formula has been entered as a replacement for the original formula in cell F7 (this is the 'Balance' column)

To break it down, I will highlight the parts as we go through them

First of all we check to see if cell E7 has anything at all in it (that is, it is not empty - note that a 'space' entry is not an empty cell), as is highlighted below.  Why do we check E7?  If E7 is empty then there is no transaction.

=IF(E7="";"";IF(OR(D7="C";D7="c");E7+F6;IF(OR(D7="D";D7="d");F6-E7;"WHAT!")))

Next we check if the cell E7 has an Upper Case 'C' or lower case 'c' in it. If it has then we add the content of E7 to the balance from F6.  Again this is highlighted below.  You will notice the use of the OR function which checks the entries inside the bracket for an entry which is true, if one or more are then the 'E7+F6' part is 'executed' (which means 'used' or 'run' or 'done').  If no entry is true then the next part of the calculation is done (which means 'used' or 'run' or 'executed')

=IF(E7="";"";IF(OR(D7="C";D7="c");E7+F6;IF(OR(D7="D";D7="d");F6-E7;"WHAT!")))

Now if the testing has got this far then a further check is made on the contents of cell D7 for an Upper Case 'D' or lower case 'd' (using the OR function again).   The highlighted section is the part to check out.
If a 'D' or 'd' is found then the contents of E7 are subtracted from F6.  Note that if the formula was E7-F6 we would get a very different - extremely different answer so make sure that you always check subtractions and divisions CAREFULLY or you could end up with some pretty weird results.  This was the second item you should have noticed from the end of the last post.

=IF(E7="";"";IF(OR(D7="C";D7="c");E7+F6;IF(OR(D7="D";D7="d");F6-E7;"WHAT!")))

Finally we have a SHOUT 'WHAT!' if the value is anything other than

• an empty cell
• a 'c' or 'C' entry
• a 'd' or 'D' entry

=IF(E7="";"";IF(OR(D7="C";D7="c");E7+F6;IF(OR(D7="D";D7="d");F6-E7;"WHAT!")))

If you see this check the column D entry and note also this will affect every entry in the F column after the error so it does continue to SHOUT at you.

That's all this time people

Next time we will look at a little inbuilt conditional formatting for colour etc..

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: