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

 

[NateO]

Hello,

I can't explain why, but I can replicate this with named ranges. Try array-entering the function.

And, you don't need *--, * will suffice.

 

[bolo]

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

HI,

Think you need to checked your named ranges, perhaps you are missing a $ somewhere, meaning that the named range is dependent on the location of the cell.

Regards

Bolo

 

[NateO]

I thought the same thing, Bolo.

But unfortunately, I can replicate this will full-on absolute referencing, in XL 2010. I don't understand why this is happening. Array-entering the function appears to help.

HI,

Think you need to checked your named ranges, perhaps you are missing a $ somewhere, meaning that the named range is dependent on the location of the cell.

Regards

Bolo

 

[atodorovic]

Hello,

I can't explain why, but I can replicate this with named ranges. Try array-entering the function.

And, you don't need *--, * will suffice.

Thanks Nate. I'll correct the formula.

--aleks

 

[atodorovic]

I thought the same thing, Bolo.

But unfortunately, I can replicate this will full-on absolute referencing, in XL 2010. I don't understand why this is happening. Array-entering the function appears to help.

It's really bizzare, I was thinking that it's perhaps the range of the named ranges as they span from B3-B65550 (as an example), shortened that earlier, but it didn't clear it up, what I've done now, as per Bolo's suggestion, is to redefine all of the ranges again, and that seems to have cleared things up.

Many thanks,
--aleks

 

[NateO]

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.

 

[atodorovic]

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.

Already started to re-enter then formulas with {}

Thank you very much, I can now finally get this sheet completed and submitted for folks to use.

--aleks

 

[NateO]

Well, I wouldn't normally recommend this, it's like driving nails with a sledge hammer. But apparently, from time-to-time, that's called for.

Never did like Named Ranges, personally...

 

[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 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