Starting with formulae in Excel – the SUM function

One of the beauties of Excel is how easy it is to do simple calculations. If you have not used any formula at all in the past, do refresh your memory on the address of cells and the simple use of “+” (plus) – click here to view my earlier post on this - http://computertipsforbeginners.blogspot.com/2011/06/what-are-spreadsheets-in-simple-terms.html.

Now the bit about  =A1+A2  works with all addresses and any number of cells as also with all the usual mathematical formula i.e. wherever you want the result to appear or be displayed, you can use say =C7*D5+F11-G67, or whatever you want to multiply (* is used for multiply), add, subtract, divide etc. Frequently however you need to add up a long column – imagine typing out =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 .... going on and on till you reach say A75 or wherever you wanted to reach. So the first formula to start with is SUM. All you need to do is type =SUM(a1:a75) and it is done.  Quick and fast was it not?

While using formulae, the essentials to be remembered are:

1.       All formula must start with the equal to sign i.e. with  =

2.       The word SUM or whatever function you use has to be typed exactly, no change whatsoever allowed in its spelling and no extra spaces anywhere.

3.       Make sure that the cell where you are tying the formula does not feature within the range i.e. you CANNOT type this formula in any of the cells lying between A1 and A75 – if you do the system will go into a loop and will hang.

4.       In any formula, the number of opening brackets or ”(“   MUST, absolutely must, equal the number of closing brackets “)”.

5.       The cell address a75 is the same as A75 i.e. don’t bother about using capitals; Excel will automatically do it for you.

6.       If you copy this formula (CTRL+C followed by paste - Ctrl+V) into say one adjacent cell on the right, Excel will automatically convert the formula into one cells on the right .i.e. it will become =SUM(b1:b75). To avoid this, read further on in the post.

7.       See the space just above the spread sheet – next to the “fx”. Yyou will see the formula bar where the formula is also visible – it is a good idea to type here, especially when making corrections.

The software automatically or by default, changes formulae in reference to the cell to which it is copied. Now say you had typed =C7*D5+F11-G67 in cell G18. When you copy it in say cell M25, it will become =I14*J12+L18-M74  i.e. each cell has changed position of the row and the column so as to retain the same relative position to the destination cell M25.This is usually an excellent thing, which you will find as you go along. But if you do not want this to happen, you need to tell the software so, and this is very easily done by using the “$” sign. By way of example, take the cell at F11 and follow these rules

1.       C7*D5+F11-G67  changes all the cells as indicated above to I14*J12+L18-M74  

2.       C7*D5+$F11-G67  fixes the position F and changes all the others, the value in M25 then becomes  I14*J12+F18-M74  (or rather I14*J12+$F18-M74)  

3.       C7*D5+F$11-G67  fixes the position 11 and changes all the others, the value in M25 then becomes  I14*J12+L11-M74  (or rather I14*J12+L$11-M74)  

4.       C7*D5+$F$11-G67   fixes the both the positions F and 11 and changes all the others, the value in M25 then becomes I14*J12+F11-M74 (or rather I14*J12+$F$11-M74).

The dollar sign can be used to fix any position of any cell. Incidentally, to add the dollar sign, place the cursor on F11 In the formula and play around with the F4 key on the keyboard.