Excel Formulas & Functions

Formula = a calculation you enter yourself

Function = a pre programmed formula, commonly shortened to ‘fx’

Understanding Formulas

When you enter a formula into Excel you have to let Excel know that it is a formula and that it needs to do something. The way to do this is to put an = sign in the cell first, nothing will happen with it otherwise!

To perform a calculation you need to enter each figure into it’s own cell and then have a cell with the calculation in.

E.g. 5 + 6

Enter 5 into cell B2, enter 6 into cell B3

In cell B4 you can enter the calculation   =B2+B3

Excel will automatically calculate the total. When you click on cell B4 you can see the formula in the formula bar, prefixed with fx.

ff 1

This seems like a lot of work for a simple calculation but once you have done it, you can change the numbers in cells B2 & B3 and Excel will always give you the answer in B4

This format can be used for all calculations – addtion, subtraction, multiplication and division.

You will need to know the symbols that Excel/computers use for multiplication and division as they look slightly different:

Multiplication   *             Division   /

E.g. 20 divided by 5 looks like 20/5  and 3 multiplied by 7 looks like 3*7

For more complex calculations, you need to use the BODMAS rule of arithmatic as that is the order Excel uses.

bodmas

A simple calculation can have two answers unless BODMAS is applied.

e.g. 5+ 2 * 10     could equal 70 or 25. The correct answer is 25

The multiplication is 2nd level so do that first and then do the level 3 addition

A slightly more complicated example:  3 – 4 /2 + 6*5

You have to to the multiplication and division BEFORE any addition and subtraction

4/2 = 2

6*5 = 30

So it now looks like this  3 – 2 + 30

Addition and subtraction are completed from left – right so the answer is 31.

If there are brackets in a calculation, they have to be worked out first, everything else still follows BODMAS

10+(10*2)/5 = 14

(10*2) = 20 – 1st level

20/5 = 4 – 2nd level

10+4 = 14 – 3rd level

 

Understanding Functions

Functions are there to make using large formulas easy. Imagine having to add up 100 cells, that’s an awful lot of typing and the chance of making an error increases.

To make life easier this and many, many more formulas covering a wide range of categories from statistics through to engineering have been added in Excel as automated functions.

The sum function, for instance, can reduce this:

=E8+E9+E10+E11+E12+E13

to this:

=SUM(E8:E13)

Under the ‘Formulas’ tab in Excel you will find the ‘Function Library’  where you can choose from all of the available functions divided up into their categories

functions library

From here you can click on insert function, the FX button, and this box will pop up:

insert fx

Here you are able to search for a function to do what you want or search the list in the category section to find it. You can also list all available functions. I have just Googled how many functions there are in Excel 2010 and it says 400!

If you are familiar with the function you want to use you can just type it directly into the cell. You still need to put the ‘=’ sign in first, the same as you do with a formula.

How to Use the SUM Function to Add

This is the most commonly used function in Excel and there are so many ways to use this, I will show you the way I find easiest.

When you have clicked in the cell where you want the total to be, click on ‘AutoSum’ and then ‘Sum’. This will insert the function.

autosum

autosum 2 Excel 2010

As you can see, it puts a dotted line (marquee) around the cells it thinks you want to add, if this is correct press the ‘Enter’ key. If it is not, if you hit ‘Backspace’ it will remove the cell references but keep the function. You can then either highlight the cells you want with your mouse, or enter the range manually.

To enter it manually

  • Type =sum(
  • either type in the first cell name or click on the cell
  • Click on or type in the next cell to sum
  • Type ) and press ‘Enter’
  • NB if you are typing the cell names in, you will need commas between each cell name.

Summing Non-Contiguous Ranges & Cells

What is non-contiguous?? It means ranges and cells that are not next to each other, they can be spread all around your worksheet and you can sum as many of them as you wish.

non contig

 

To sum the cells in the picture above I first clicked in cell B26, type in =sum(   , or click on the the sum button, hold the ‘CNTRL’ key down and click on B9, B14, B19 and B24 and press enter.

To save entering the almost identical formula into each column to total them, you can drag the formula across the cells and it will automatically adjust to sum the cells in the same rows but in the new columns. To do this click on the cell with the formula and hover over the bottom right-hand corner a little + sign will appear, when it does, hold down the left mouse button and drag the formula across as many cells as you need it.

Unfortunately the + sign doesn’t capture in the screen shot below.

drag formula

 

Calculating an Average, Finding the Max and the Min Values.

The AVERAGE function allows you to calculate the average value of a range of cells. It is used in the same way as SUM

Average

MAX and MIN both work in identical ways, I have highlighted the cells in blue and green to show it picks up the values from the selected ranges.

MIN

 

 

Share on LinkedInShare on FacebookTweet about this on TwitterShare on Google+Email this to someone

Related courses