Refusal to calculate

[Guest]

When my team tells me that their formula gives the wrong answer, I tell them
that they wrote their formula wrong, but this one...

I have a sumproduct formula that uses named ranges:
=SUMPRODUCT((hCountry=$B9)*(hIndustry=L$8)*hFees)

This formula returns zero, but the exact formula copied one cell to the left
returns the value 10. In fact, my sumproduct formulas from Columns A to K
calculate correctly - those to the right of column K return zero, and there
is nothing wrong with the formula (really...). It's as if Excel's memory can
only handle a certain number of cells

Any ideas?

thanks
Daniel

 

[davesexcel]

is this an array formula??

once the function has been entered you must press
Ctrl shift enter for the formula to work...

this is the first thing I thought of!!

 

[Charles Williams]

It is true that there are limits to the number of array formulae.

Test by deleteing columns a to k and see if column L starts working

If so you may need to move some formulae to a different sheet.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com