February 2, 2018

January 2, 2018

September 22, 2017

August 25, 2017

August 11, 2017

July 28, 2017

July 14, 2017

June 30, 2017

June 16, 2017

June 2, 2017

###### Recent Posts

###### Featured Posts

**Formulas not calculating**

14 Jul 2017

One problem that we are asked about on a fairly regular basis is to do with formulas not calculating in a spreadsheet.

We will not go in to all the reasons why formulas are not calculating but will look at the two most common solutions to the problem (I would say that the two solutions are responsible for about 90% of the cases we see).

Cells formatted incorrectly

Cells that are not formatted as numbers will be ignored in any calculation in Excel. In the example below cell E2 has been formatted as Text and as a result the total in cell E6 in the 'My List 2' column ignores this Text, even though cell E2 looks like a number, and only adds the subsequent two numbers in the list.

Simply change the format of the cell to General, or whatever format you use, will resolve this problem and ensure that the total is correct and calculated.

Workbook is set to Manual Calculation

Workbooks are sometimes set to Manual Calculation. This often occurs when a workbook is very large and contains a significant number of calculations, which are often complex. By setting the workbook to Manual calculation it helps to ensure that processing isn't slowed down each time a change that is made.

The Calculation Options for manual (and automatic) calculations are found on the Formulas tab

When Manual calculation is selected Excel won't automatically update formulas when changes are made to the spreadsheet.

To update the formulas you have to 'force' the calculation, either by pressing F9 in the cell, pressing the calculate button or by turning Automatic calculations back on.

These two solutions should resolve most instances of spreadsheets not calculating, but there may be other reasons and please contact us if you have any particular problems.

Tags: