Using cell references in sumproduct formula

K

Kim Locklin

Hi -

I am trying to use cell references in a formula instead of hard numbers.

Here¹s the formula:
=SUMPRODUCT(($C$3>{0;1200000;1600000;2000000})*($C$3-{0;1200000;1600000;2000
000})*{0.05;0.01;0.01;0.03})

Instead of ³1200000² I want to use ³$c$1²
I keep getting an invalid formula message. I have tried removing the $,
adding ( ) to the cell reference ­ to no luck.

Any suggestions?

Thank you!

Kim
 
A

Anon

Kim Locklin said:
Hi -

I am trying to use cell references in a formula instead of hard numbers.

Here¹s the formula:
=SUMPRODUCT(($C$3>{0;1200000;1600000;2000000})*($C$3-{0;1200000;1600000;2000
000})*{0.05;0.01;0.01;0.03})

Instead of ³1200000² I want to use ³$c$1²
I keep getting an invalid formula message. I have tried removing the $,
adding ( ) to the cell reference ­ to no luck.

Any suggestions?

Thank you!

Kim

It's not the SUMPRODUCT that's your problem, it's the array constants (e.g.
{0;1200000;1600000;2000000} ).
An array constant can only contain constants, not cell references. See Help:
"Items that an array constant can contain".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top