Calculations in Excel

Learn the 4 steps that make entering a calculation into an Excel spreadsheet work reliably every time.

Never entered a calculation into an Excel spreadsheet? When you’ve finished this short tutorial, you’ll be able to create formulas in Excel to make your business life just a little bit easier…

The video refers to buttons below – these only appear when viewing from the www.tekbites.com website.

We’re just getting started (this is our first video) so the resources available on the tekbites.com website will be quite limited to begin with – apologies in advance.

The first Excel mistake everyone makes

Most people who want to produce an Excel spreadsheet make their first mistake when they fire up Excel.

So why is that a mistake and what should we be doing instead?

What we all do when creating a spreadsheet is to start up Excel, enter some row headings, enter some column headings, put in some data – real or sample – and then analyse the results with some calculations. This inevitably means the results or ‘answers’ will be far over to the right hand side of the sheet or way down at the bottom.

How many of your spreadsheets have the answers way down the sheet at some arbitrary row. And that always needs moving as soon as you add or remove data.

The best place for the answers is always at the top left hand corner of the sheet because that’s always the easiest place to get to. And you don’t need to keep on moving it.

I think creating a spreadsheet is a four step process.

Step 2 is switching on Excel and actually building the spreadsheet.

Step 1 is grabbing a pen and paper and sketching out a design for your spreadsheet. Take the time to decide what your output needs to look like and you’re far more likely to have a working and workable spreadsheet.

What questions are you answering? How are people going to use the information you’re collecting and analysing. Which bits of the data need to be the most visible? Always think about the output first.

And don’t feel you have to think (or act) conventionally because many of the reasons we do things the ‘usual’ way have more to do with pen and ink than modern day electronics.

Totals, and other statistics, often end up at the bottom of columns of numbers because that’s where they always appear, but the original reason for putting the total at the bottom has far more to do with adding up the way you did when you first went to school than any sort of conscious decision.

If you put the totals (and other summary statistics) at the top of the sheet, above the data, you can lock the first few rows on the screen using Freeze Panes so any changes to the data are immediately reflected in the summary statistics. You also get the added benefit that the answers are easy to find and you don’t have to keep on moving them every time you add some data.

If you do spend some time designing your spreadsheet before you start building it, you will definitely save yourself time, effort and aggravation in the long run.

And the two missing steps – that’s for another post!

Percentage difference between two numbers in Excel

We served 2,800 customers last month and 3,200 customers this month. What percentage increase is that?

This calculation requires three steps.

First calculate the difference between the two numbers. Then, calculate this value as a ‘fraction’ of the earlier number. Finally ask Excel to display this result as a percentage.

The difference between the two numbers is always the later or more recent number minus the earlier or older number.

In the example below, customer numbers have increased from 2,800 last month to 3,200 this month. First, calculate the difference.

Next, take the difference between the two numbers and divide by the original number.

Finally, change the format of the answer so that Excel displays it as a percentage. This can be done using the percentage icon on the toolbar.

It is usually worthwhile increasing the number of decimal places displayed so that a more accurate value is shown.

Customer numbers changing from 2,800 to 3,200 represents an increase of 14.29%.

 

We served 3,500 customers last month and 3,400 customers this month. What percentage decrease is that?

Where the number decreases, the steps are the same.

In the example below, customer numbers have decreased from 3,500 last month to 3,400 this month. First, calculate the difference.

Here, the difference is a negative number, representing a decrease in numbers.

Next, take the difference between the two numbers and divide by the original number.

Finally, change the format of the answer so that Excel displays it as a percentage. This can be done using the percentage icon on the toolbar.

Again, it is usually worthwhile increasing the number of decimal places displayed so that a more accurate value is shown.

Customer numbers changing from 3,500 to 3,400 represents a decrease of 2.86%.

Try it yourself using the examples below…

Download this example

Download all percentage examples

What percentage does a value represent of the total amount?

7 answers out of 12 were correct. What is the percentage score?

This type of calculation is best approached as a fraction.

The partial value, in this case 7, is divided by the total value. Formatting is then used to display the resulting number as a percentage.

In the example below, 7 answers were correct out of a possible 12.

Finally, change the format of the answer so that Excel displays it as a percentage. This can be done using the percentage icon on the toolbar.

It is usually worthwhile increasing the number of decimal places displayed so that a more accurate value is shown.

A score of 7 correct out of 12 questions represents a total score of 58.33%.

Try it yourself using the examples below…

Download this example

Download all percentage examples

Decrease a number by a percentage

Calculate a discount of 9% on a purchase of 120

This is best approached in two stages.

First, calculate the percentage required using the technique described here – Finding a percentage of a number. Then subtract this amount from the original.

In the example below a 9% discount is applied to a purchase of 120.

If you get a 9% discount on a purchase of 120, you only pay 109.20.

Try it yourself using the examples below…

Download this example

Download all percentage examples

Count using two conditions

How many hats in this list are red?

There are several ways in which this problem may be solved. The simplest is first to determine which of the rows in the table contain Red Hats. This is done using the logical function AND.

The AND function needs two or more parameters. In this case there are two questions to be answered. ‘Is the item a hat?’ and ‘Is the item Red?’.

For the first row, these translate into B3 = “HAT” and C3 = “RED”. Once again the text must be enclosed in quotes and the match is not case sensitive.

If all the criteria in the AND function are met, the function returns a TRUE value.

The calculation is then copied for each of the other rows in the table.

Any row containing a Red Hat will have a value of TRUE. To find the number of Red Hats, COUNTIF is used to count all entries with a value of TRUE.

TRUE need not be enclosed in speech marks as it is a logical value and not a text value.

Since the individual TRUE and FALSE values do not need to be seen, the answer can be moved to E14 and column D can be hidden.

Try it yourself using the examples below…

Download this example

Download all counting examples

 

Using COUNTIF to count specific items in an area

How many of the items in this list are red?

The COUNTIF function can be used to count only certain specific entries in a list. It requires two pieces of information.

The area containing the items to be counted and some way to tell which entries are required.

In the example below, which was created in the UK in the dead of winter, the colours of the items to be counted are contained within the range C3 to C12

If only the red items are to be counted, these are identified using the word RED. To indicate that this is literally the word RED, the text must be enclosed within speech marks.

Please note that a text match of this type is not case sensitive.

To find out how many hats are in the list, a similar procedure is followed. The only difference is that the item type is listed in the range B3 to B12. Once again, the text criteria is enclosed in speech marks and it does not matter whether upper or lower case text is used.

And if you want to know how many red hats are in the list, click here

Try it yourself using the examples below…

Download this example

Download all counting examples