Using the fx key to debug formulas

[Bill]

I'm working with the SUMPRODUCT function.

If I highlight a cell containing a SUMPRODUCT formula, & press the formula (paste
function) key 'fx', it shows me the arrays I've entered. Now, it also shows me the first
array RESULTS to the right of each formula, like
={0;0;1;0;0; ... and so on.

This is very useful. Is there a debug feature, or some way, for me to see more of the
array results?

Thanks,

Bill.

 

[Biff]

Hi!

You can select the cell with the formula, then, in the formula bar use your
mouse to highlight the expressions you want to evaluate then hit function
key F9. There's a limit as to how many array elements it will display,
though. I'm not sure what that limit is but I just tried it on an array of 2
columns by 250 rows and it kicked up a message: Formula too long.

Another option if you use Excel 2002 and up.......

Select the cell with the formula.
Goto Tools>Formula Auditing>Evaluate Formula.

This allows you to step through the formula exactly as Excel does. I use
this a lot. It's great for finding problems. One thing, however, at least it
happens to me, if you evaluate certain types of "complex" array formulas it
causes Excel to crash. I can crash Excel at will!

Biff