Cells Not re-calculating despite autocalc on

[Trick]

I am an advanced excel user and this is the first time i have encountered this problem.

My xls contains a mixture of arrays, lookups and sumif functions.

MY array is pulling through the updated information (eg. a sum of spend on a category), and this in turn feeds to a total by quarter. HOwever the simple formula which adds the three months of the quarter is not updating.

However, if i go to the cell with the forumla (which is as simple as a1+a2+a3) press F2 to edit, then press enter, hey presto it calculates?

Weird.

any ideas?
I dont want to go into each cell and do that as there are thousands!!

[Gord Dibben]

Try this.

Select the cells.

Edit>Replace

What: =

With: =

Replace all.

This is same as F2 and Enter on each cell.


Gord Dibben MS Excel MVP

On Thu, 27 Jul 2006 13:30:09 -0400, Trick
<Trick.2bm78f_1154021706.4679@excelforum-nospam.com> wrote:

>
>I am an advanced excel user and this is the first time i have
>encountered this problem.
>
>My xls contains a mixture of arrays, lookups and sumif functions.
>
>MY array is pulling through the updated information (eg. a sum of spend
>on a category), and this in turn feeds to a total by quarter. HOwever
>the simple formula which adds the three months of the quarter is not
>updating.
>
>However, if i go to the cell with the forumla (which is as simple as
>a1+a2+a3) press F2 to edit, then press enter, hey presto it
>calculates?
>
>Weird.
>
>any ideas?
>I dont want to go into each cell and do that as there are thousands!!

[Niek Otten]

Here's my standard checklist:
=========================================================
Cells not calculating (correctly)
Niek Otten, March 31, 2006

Most frequent causes:

1. Calculation is set to Automatic. This often happens unintentionally, for example by opening another workbook first.
Calculation is an Excel-wide setting; the first workbook opened determines the calculation mode, which then applies to all
open workbooks and workbooks that are opened later in that instance of Excel. It can be changed manually (again, for all open
workbooks):
Tools>Options>Calculation tab, check Automatic.
2. There are User Defined Functions (UDFs) which access cells directly from within the function, that is, not via the
argument list. Then Excel is not aware of the need of recalculation if the precedent cell changes. You can include
Application.Volatile in the function, but there is no guarantee this will always calculate cells in the correct sequence in all
(future) versions of Excel. It also causes the calling cells (and dependents) to always be recalculated, even if not necessary.
Really the best way is to include all precedent cells in the argument list.
3. You see the formulas in the cell, not the answers. Two options: a. Tools>Options>View, Formulas is checked (you may
accidentally have hit the shortcut key: CTRL+`), b. the cell was formatted as text before you entered the formula. Format as
General and re- enter (F2, ENTER). If it's about many formulas, select them, Find and replace "=" by "=". That's more or less the
same as re-entering them.
4. Excel version 5.0a (yes, very, very old!) has serious recalculation bugs, Excel 97 absolutely needs Service Pack 2
(SP2) to calculate correctly.
5. Very, very rarely, Excel's dependency tree gets messed up. One way to rebuild it is to find and replace all "=" by
"=", for all sheets (for Excel, this is like re-entering all formulas). Later versions of Excel rebuild by pressing
CTRL+ALT+SHIFT+F9
6. For many calculation secrets, visit Charles William's site:
www.decisionmodels.com
=========================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Trick" <Trick.2bm78f_1154021706.4679@excelforum-nospam.com> wrote in message
news:Trick.2bm78f_1154021706.4679@excelforum-nospam.com...
|
| I am an advanced excel user and this is the first time i have
| encountered this problem.
|
| My xls contains a mixture of arrays, lookups and sumif functions.
|
| MY array is pulling through the updated information (eg. a sum of spend
| on a category), and this in turn feeds to a total by quarter. HOwever
| the simple formula which adds the three months of the quarter is not
| updating.
|
| However, if i go to the cell with the forumla (which is as simple as
| a1+a2+a3) press F2 to edit, then press enter, hey presto it
| calculates?
|
| Weird.
|
| any ideas?
| I dont want to go into each cell and do that as there are thousands!!
|
|
| --
| Trick
| ------------------------------------------------------------------------
| Trick's Profile: http://www.excelforum.com/member.php...o&userid=36860
| View this thread: http://www.excelforum.com/showthread...hreadid=565704
|

[Trick]

Thank you.

I`ll give that a whirl.