reporting bugs in Excel?

**moonfish** · 05-02-2011, 08:48 AM

I've been meaning to find a way to report a bug in Excel to Microsoft but it seems this isn't possible. To try and bring it into the open I figured I'd find a forum and post about it. You might find it as interesting as I do. I've seen this happen in Excel 2007 under windows XP, not sure about other versions or operating systems.

First off, configure Excel to perform workbook calculations manually (Excel Options > Formulas > Workbook Calculation > Manual).
The easiest way to simulate a cell where calculations are not up to date is by expanding a formula to multiple cells so create something like this.

   A         B         C
1  2         =na()     =and(isna(A1), isna(B1))
2  =na()     =na()

The value in C1, when pressing enter, will be FALSE. Select C1 and drag the formula into C2. The value in C2 will be FALSE as well (as it's supposed to since calculations are set to manual).
Now select C2 and go to Formulas > Evaluate Formula. This will show the formula AND(ISNA(A2),ISNA(B2)) which breaks down to AND(TRUE,TRUE). However, when you hit evaluate one more time, it will say the value is FALSE because instead of calculating it further, it will refer to the current cell value.

This really bothers me. What good is a formula evaluation tool if it doesn't evaluate formulas properly?

**Richard Buttrey** · 05-02-2011, 09:37 AM

Hi,

It's a feature not a bug.

You could try instead F2 and then F9 to see the formula result rather than using the evaluation function - which is quicker than pressing evaluate a few times.

Regards

**moonfish** · 05-02-2011, 10:05 AM

I'm saying there is a bug in the evaluate formula feature. I know I can get the formula outcome by hitting F9.

Evaluate Formula is supposed to evaluate the formula from beginning to end, walking through each step in the calculation process in the order that Excel would normally do.
Instead, it walks through every step except for the last, where the programmers forgot to take into account the option for manual calculations.

**Richard Buttrey** · 05-02-2011, 10:25 AM

Hi,

What makes you so sure it's a bug and that MS didn't expect it to work this way?

If you look at the Help it starts off...

"You can see the different parts of a nested formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) evaluated in the order the formula is calculated.

I agree it's maybe not particularly well written but in MS's defence it does talk about Evaluating nested formulae.

There is at least arguably a difference between 'evaluating' a formula to see what results different parts of a nested formula produce, and having the whole formula result written to the cell. And furthermore if you've consciously set calculation to manual why would you want the whole formula result to change unless you take specific action like calculating a sheet?

I think the jury's out on whether it's a bug.

Regards