Formulas not calculating

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.

#FormulaNotUpdating

Featured Posts
Recent Posts
Archive
Search By Tags
No tags yet.
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square

 

 


E: Office@iFRC.co.uk

 

T: (01889) 883860

 

 

CONTACT US:

 

iFRC Limited

1 Abbey Gardens,

Great Haywood,

Staffs. ST18 0RY

 

© 2015 iFRC Ltd