Advance Sumproduct calculation

[Guest]

Column C has numeric values
Column F has numeric values
Column M has numeric values

What I would like to do somthing like the following:

=SUMPRODUCT(MIN(C10:C21,(1099.41-F10:F21)),M10:M21)

In English - subtract each value in column F from 1099.41, come up with a
stream of 12 numbers compare these twelve numbers to the 12 numbers in column
C and use the lessor (by row) and multiply this result by the values in
M10:M21 (if the lessor of the two number is negative , zero will be used)

Example
Column C Column F

100 100 .08
100 200 .08
100 300 .085
100 400 .085
100 500 .085
100 600 .095

Subtracting 100 from 1099.91 = 999.91 compare that to 100 the lessor of the
2 is 100. Multiply number by .08 to yield 8

The final number would compare 99.91 to 100 the lessor of the 2 is 99.91.
Multiply this number by .095 to yield 9.49

The number in column C will not always be constant but Column F will always
be the sum of column C.

 

[vezerid]

=SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21)

HTH
Kostis Vezerides

 

[Guest]

When I keyed this in and do formula evaluator - it does not like the first
c10:c21 statement - it gives me a value error

 

[vezerid]

I reproduced the conditions and it worked fine. Are you getting a
result?

 

[Guest]

No - it is giving me a value error

for add-ins I have
Analysis toolbox
Analysis toolbox - VBA
Lookup wizard and
solver add-in

Checked

My guess is it doesn't like the if

 

[vezerid]

I don't see a reason why... As I said I reproduced the condition
exactly. Anyway, hope someone jumps in.

Kostis

 

[Harlan Grove]

Brad wrote...

No - it is giving me a value error ....
My guess is it doesn't like the if
Correct.

....

They you must have entered it as an array formula. You didn't mention
that step.
....

IF is one of the few, old functions that REQUIRES entry as an array
formula in order to process arrays. So enter the formula above holding
down [Ctrl] and [Shift] keys before pressing [Enter].

Purely academic, you could avoid array entry using

=SUMPRODUCT((C10:C21<1099.41-F10:F21)*C10:C21
+(C10:C21>=1099.41-F10:F21)*(1099.41-F10:F21),M10:M21)

 

[Guest]

I'm using version 2003

I don't see a reason why... As I said I reproduced the condition
exactly. Anyway, hope someone jumps in.

Kostis

 

[Guest]

Found that that this was entered as an array equation - that part I missed

Brad wrote...

No - it is giving me a value error ....
My guess is it doesn't like the if
Correct.

....

They you must have entered it as an array formula. You didn't mention
that step.
....

IF is one of the few, old functions that REQUIRES entry as an array
formula in order to process arrays. So enter the formula above holding
down [Ctrl] and [Shift] keys before pressing [Enter].

Purely academic, you could avoid array entry using

=SUMPRODUCT((C10:C21<1099.41-F10:F21)*C10:C21
+(C10:C21>=1099.41-F10:F21)*(1099.41-F10:F21),M10:M21)