12 August 2013

Correct Excel Formulas From Start

Excel iconThe Tips section has a new Excel tutorial, and this time I demonstrate the basics of Excel formulas. If you are not already used to working with formulas, I recommend you to watch it.

You can do a lot with Excel if you know how to make the program count numeric values for you, but there are some pitfalls you should be aware of.

Calculate with Excel
The main reason for Excel's popularity is its capacity for calculations that can be used in  different kinds of dynamic presentations. Excel formulas tell the program what cells to use and what to do with the numeric values in those cells.

Thanks to the formula you can quickly see what happens if you change a value in one or several cells, because the result of the calculation will change automatically.
Excel sum formula

Make prognoses with Excel
As Excel makes it so easy to see what happens when values are changed, the program is often used for prognoses. We are all aware that a prognosis is not a true fact but just a qualified guess about the future, but I think we too often assume that the selection of data and the mathematics behind the prognosis are correct. But that is not always the case.

Student discovered mistake in famous white paper
Some months ago we could read about a grave mistake in Excel calculations made by an economy professor and a former chief economist of the International Monetary Fund. In 2010 they presented a research paper called Growth in a Time of Debt, which was widely spread and influenced politicians all over the world.

The paper claimed that growth slows down dramatically  in an economic crisis when the size of a country's debt rises above 90% of Gross Domestic Product. The study attracted much attention, as it seemed to answer the question about how to act in a crisis – stimulate economic growth or cut spending and raise taxes? – with the advice "hold on to the money".

When a student failed to replicate the findings he finally asked for the Excel spreadsheet used by the experts, and as he studied it he found an error. When it had been corrected the prognosis was modified considerably!

I think this story is worth remembering, because it shows how important it is to:
  • include the correct data in the calculation
  • create the correct formula for the calculation
Excel is an excellent tool, but you have to understand the mathematics behind the formulas. Excel does not do the math for you, and it certainly does not tell you what data is relevant to include in the calculations.

That said, I urge you to try Excel formulas if you have not already done it. In my demo I try to explain the logics behind the formulas, so I hope it will give you a good start!

Later I will record another tutorial with more advanced formulas, and you can already find several Excel demos on how to create graphs from tables with numeric values in the Tips section.

By Peter Kalmstrom
CEO and Systems Designer Business Solutions