Personal Accounts - Conditional Formatting

Hi People

In the last entry, we discussed the options for detecting debits and credits on our simple Personal Accounts spreadsheet.
And having decided on the extra column to make the user think about what is to be done, we require some alteration for the calculations in column F (after the insertion of the new column D), from F7 (not F6!) down.

Currently the calculation in F7 looks like this

=IF(E7="";"";E7+F6)

[ Note  how the values have been changed.
Before the insertion of the new D column the calculation looked like this
=IF(D7="","",D7+E6) ]

First of all we need to test the letter in column D for the letters C,c,D,d.  Any other entry should produce a bold error message.

You will notice that we check for capital C and normal c (Upper Case = Capital, Lower Case = normal) and also for Upper Case D and Lower Case d.  This is because we do not know if the user works with Upper Case or Lower Case letters, so we cover both possible entries.

The error message should be immediately obvious so I suggest a word all in Upper Case letters. I have chosen the word WHAT! to make it stand out.  We shall make it stand out even more later, with conditional formatting

OK take a look at the formula below.  Yes I think a number of you will experiencing a sharp intake of breath, but when broken down into its parts it is really quite simple.

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

I am going to leave you with this formula to have a look at for next time and then we will break it down to show you what it means.  You will notice two things specifically (if your very sharp), one of which is not so obvious, but its effect would be alarming for the user.

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: