Sales Analysis using IF, a FREE Business Spreadsheet Tip from Working Data

In Figure 1, we see the sales figures for 2007 as compared to 2006. In column B, the column headed "Item", are listed the products we are comparing. In columns C and D, we have the quantities sold for each product for those two years. In column E we show the percentage increase from 2006 to 2007.

Spreadsheet Data. 1

We want to identify all those products with a sales increase of 20% or more as a "High Flyer". To do this, we write an IF statement. In Spreadsheet Data 2., we write the heading in column F, of "High Flyer".

Spreadsheet Data. 2

Now in the first cell under the "High Flyer" heading, we type in the formula containing the IF function. With the IF function we are asking our data a question which has a YES or NO answer. Depending on whether the answer is yes or no, we will instruct the spreadsheet to give us a different output in the cell. If the percentage increase is 20% or more, we will say, put in the text "High Flyer", otherwise, just leave the cell blank.

Spreadsheet Data. 3 shows the formula we type into the cell. =IF(E7>=20,"High Flyer","")

Spreadsheet Data 3.

The way we would read that formula is, 'If E7 (the percentage increase) is equal to or greater than 20, then print "High Flyer", if not, print "", which is nothing.'

Because "High Flyer" has quotation marks, Excel knows that it is text and will not attempt to do any calculation with it. If we wanted a number to be put into the cell, we wouldn't use the quotation marks.

To finish off, all we need to do is copy the formula down the rest of the column, either by clicking and dragging, or double-clicking the black cross hair on the bottom right corner of the cell, as in Spreadsheet Data 4.

Spreadsheet Data. 4

Next month we will take the IF function on a further stage and look at nested IF statements. If you would like a specific topic covered in our spreadsheet tips section, please contact us. A list showing the archive of previous topics covered is on the left hand side of this page towards the top.

..back to top