Differing results in different cells

[atodorovic]

Hi Everyone,
I was wondering if anyone would be able to help me with this, I've got a formula (listed below) that is causing the worksheet to behave improperly.

=SUMPRODUCT(--(DS1DATE>=DATE(2011,$Q$3,1))*--(DS1DATE<=DATE(2011,$Q$3,31)),--(DS1PROJECT=$B$8),--(DS1STATUS="MIGRATED"),--(DS1CIS="AT"))

If I put this into cell L8 for example it returns a #VALUE, if I move it to cell L40 it calculates properly. Even if I try and CUT from L40 back to L8, it still flips the result back to #VALUE...

I have tried clearing the contents, resetting the formatting to no avail.

Any pointers would be greatly appreciated as it's simply driving me insane at this point!

By the way, I wan't to thank you all for the numerous amazing tips n' tricks as well as guides that are found within these forums, a truly amazing resource for Excel.

Thank you,
--aleks

 

[bolo]

The problem in Excel 2010 seems to be odder than that? Place 1's in A1:A10 and name it 'Gooney'.

Enter =Gooney in C6 and you get the expected result, cut and paste to C15, and you get your #Value error, even though F9 Evaluates the named Range properly. Which is absolutely referenced.

Now, if I CSE that bad-boy, no errors.

Same thing in Excel 2007. But i think this is correct.

The name refers to an array, and so you need more information to evaluate the formula "=gooney". When you have "=gooney" in a cell whose row is between 1 and 10, excel seems to use the row of the cell to pick the result of "=gooney" (change A1:A10 = 1,2,3,etc, and you will see what i mean)

So what excel is essentially doing is evaluating the formula =index(gooney,row(C6),) Therefore when the row is outside of 10 you get an error. seems to make sense to me.

when you CSE you just pick the first value.

Regards

Bolo

 

[atodorovic]

I hate them as well, but had to resort to them in this case to make the formula somewhat readable, as I've got a matrix of 30x12 that has a different formula for each cell

So far so good!

Thanks
--aleks

 

[NateO]

Further testing leads me to agree with that. Although, I'm not sure I'm crazy about that...?