Personal Accounts - Conditional Formatting - part 3

Hi People

Let's have a look at the spreadsheet pictures and show you some of the things we have been discussing.

This picture shows the result of an unexpected value in D7.  See the WHAT! in F7

Notice here that the next entry still causes an error in the 'Balance' column - column F

When the errors are corrected, the system goes on working

In this picture a Credit or 'C' transaction is made, and the value of the balance goes up.  Whilst it has been tested the spreadsheet does need much more testing before being happy with the results being seen.

A large withdrawal here has caused the spreadsheet to use its internal conditional formatting.  The  -£17.17 in the 'Balance' column is in red numbers

The last picture above shows that some colour conditional formatting is used in the Currency format for numbers.  We can further enhance the conditional formatting if we wish.

We could highlight the transaction amounts if they are credits or debits for instance

Conditional Formatting is found under the 'Format' menu options, about half way down.

For what we are going to do is fairly involved, so the details we will leave until next time

Thank You for stopping by

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:

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:

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:

The Personal Accounts Spreadsheet continues

Hi People

Did anyone have a look at the possibilities for correcting the mistake over a debit being added to the balance, instead of taking it away from the balance.

Well there are a number of options here, two of which we will explore in more detail.

Firstly there is the use of minus values in the 'Amount' column, whenever a Debit is required (when money is taken away from the balance - this is known as a 'Debit'), the value must be put in with a minus sign to allow the calculation in the 'Balance' column to work correctly.

Using this method is going to require the user to make sure that the values are entered with the minus sign when required.  This will easily cause errors in entries as the minus sign will be forgotten, occasionally.

The second method will require the use of an additional column between the 'Type' and the 'Amount' columns.

This column will be used to put the letter 'D' or 'C' into the appropriate row.  The letter entered will be tested, and one of three outcomes will  result in either the Balance being credited (added to), debited (reduced) or NO Change (if any other letter or character other than 'C' or 'D' is placed in the cell).

This is the method we will adopt as it will provide some 'error checking' as well as a thought provoking entry in the new 'D' column

We will make this more obvious by using 'conditional formatting' to colour the amount depending on a '(D)ebit, (C)redit or error input (any other letter, number or character - apart from no value - that is no entry)

Are we ready to do this?

Place the mouse pointer over the boundary line between the C and D columns ( that is at the top of the columns), this will create a double headed arrow if you have in the right place.  Now RIGHT click and select 'Insert columns' and a new empty 'D' column.

All the calculations have been automatically changed to make sure they work as they did before the column was inserted.

We will change the calculation in the Balance column to create the right result for debit and credit transactions and this will be looked at 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:

Personal Accounts - Layout 2

Hi People

Been a while, but let's get down to it, you will remember that I left you with the task of playing with the formatting for the Title.  The merged block of cells at the top of the spreadsheet.

Following on from the basic formatting, let's sort out the formulae (plural of formula i.e. calculation).

Initially we want a balance to start the proceedings. So we will put the initial value of 0 in G5 (or you can put your own value in here). And  dont forget to format the cell as currency.

The Spreadsheet so far showing the starting balance of £0.00 (after formatting the cell for currency)

Now the cell E6, will contain a formula which will be unique to that cell, as it will be referring to the G5 cell, where as the remaining column calculations will be based on the cell above (e.g. the calculation in E7 will refer to E6 as the balance).

OK the calculation for cell G5 is:

=if(D6="";"";D6+G5)

The cell below will contain the formula

=if(D7="";"";D7+D6)
See below

This is the spreadsheet with the formulae entered (which would be blank without any values entered)

Looking at the picture above I have put 2 entries into the spreadsheet, showing  the calculated values, which as you can see, incorrectly calculates the cash debit on row 7. 

 The value is added to the balance from D6 instead of reducing the balance.  

Clearly we need to do something about this.  

Think about it and we will deal with this issue next time

That's all for now

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:

Personal Accounts - Layout 1

Hi People

Let's do more with the Personal Accounts spreadsheet we started last time.

First though, you may have noticed that when you start a new spreadsheet, the program always starts with 3 sheets.  This is a situation which requires a new spreadsheet to correct but we will deal with that another time.

The extra sheets can be removed in the current spreadsheet by RIGHT clicking on the tab you which to remove (the tabs are at the bottom left hand side of the spreadsheet)

A Tab at the bottom of the spreadsheet, normally there are three

Select 'Delete sheet...' from the menu and 'yes' when prompted.

Sheet gone and job done.

Also you can 'Rename' the sheet from the same menu (as I have done in the above picture).

Continuing with your spreadsheet, currently it has a very basic layout, so let's change the appearance a bit.

Highlight row 5 (left click on the 5 on the left side of the spreadsheet) and click the centre alignment button on the Formatting Tool Bar (This is the bar with the 'Default' , 'Arial' font and '10' font size boxes at the left of the  toolbar - see below)

The left hand part of the 'Formatting' Toolbar, showing the 'Default'  format setting, the 'Arial' font setting and the '10' font size'

The 'Centre text horizontally' button

Now your headings are all centred.

To make a bit more of the top of the spreadsheet, how about a nice big heading in a merged block of cells.  To merge the cells, highlight the block from B2 to F3 ( that is two rows and four colums), now press the 'Merge Cells' button

The 'Merge Cells' button

The range will now be a merged block.  Click inside the merged block and set the font size (see the picture above) to 18.  Now create you own heading or you can stick with 'My Accounts' as I have done. 

You can also set the title as centred. and let's add a bit of colour, by right clicking in the merged block of cells and selecting 'Format Cells....' from the list.  

The 'Background' tab is the one you want and select a colour of your choice. 

Also play around with perhaps making the headings bold and adding a coloured background.

Have a go and we will have a look at it next time.

Well that's it for now.

Thank You for your attention

Regards

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:

Personal Accounts Spreadsheet

Hi People

Following on from the basic Mileage spreadsheet, and with no suggestions as yet as to what you would like to see as a spreadsheet project, I felt a Personal Accounts spreadsheet, might be of use.

I would however, ask you to look over ALL the first tutorial as I will be making some assumptions on what you know, based on information from the earlier tutorial.  So please review the last tutorial fully, before getting into this one.

OK that being said (also I would like to add, that I cannot ANY responsibility for your use, misuse or abuse of this information or any errors which may creep in), let's get going.

Open OpenOffice Calc and  setup the following  in row 5 (these are the headings)

Date (B5) means put the title 'Date' in cell B5

So

Date (B5), Transaction (C5), Type (D5), Amount (E5), Balance(F5)

These will do for now we may add more columns later.

If you remember how to set Formats for the individual columns (check back to the last tutorial for more information), set the column B as a Date format, set columns E and F as currency

For now that's it

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: