Excel Formula wizard lying!

[mrbud1972]

Hi, got a curious one.(Excel 2003) We have a sumproduct formula entered within the wizard which works fine, get a result. The problem starts when you come out of the wizard we get a #value error.
Why would it look fine in the wizard then give me an error out of it?
Many thanks,
Martin.

 

[xlbo]

dunno really - you'll have to give more info

What is the formula?
What is in the ranges that it is referencing?
What type of data is it referencing?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mrbud1972]

Thanks for the reply. The formula is as follows... as you can see, its slightly more than a sumproduct.

=SUMPRODUCT(VLOOKUP($C11,Rates!$C$6:$J$12,YEAR(N$6:S$6)-2005,FALSE),N11:S11)

 

[xlbo]

would suggest you have some non numeric data in a column that the formula is attempting a math operation on but without seeing your data layout I cannot say for cetain

check your data in N6:S6 and N11:S11 for anything that may look like a number but is actually text (you can test this by using either the ISNUMBER or ISTEXT functions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Did you forget to array enter the formula using CTRL+SHIFT+ENTER??

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

With the #VALUE error displaying, hit F2 then press CTRL+SHIFT+ENTER at the same time and see if that makes a difference. If you do it correctly you will see curly braces surround your formula such as:-

{your_formula}

(These cannot be entered manually)

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mrbud1972]

LOOKS like you've hit the nail right on the head there!!
Works a treat! completely forgotten about that. nothing like making this easy is there!
Thanks very much. would never have guessed.
Thanks again,

Martin.

 

[KenWright]

LOL - Your formula construct kind of gives it away really, so that is the first place I would have looked. Anytime you use VLOOKUP in a Sumproduct, VLOOKUP only gives a single answer, so if you are using it within Sumproduct then you likely want a series of values returned based on comparing that one value with multiple others, hence an array is required.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------