Advance Sumproduct calculation

Advance Sumproduct calculation

Post by QnJhZ » Thu, 02 Nov 2006 01:08:01


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.
 
 
 

Advance Sumproduct calculation

Post by vezeri » Thu, 02 Nov 2006 02:02:01

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

HTH
Kostis Vezerides

 
 
 

Advance Sumproduct calculation

Post by QnJhZ » Thu, 02 Nov 2006 03:39:02

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

Advance Sumproduct calculation

Post by vezeri » Thu, 02 Nov 2006 05:07:24

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

Advance Sumproduct calculation

Post by QnJhZ » Thu, 02 Nov 2006 05:15:02

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
 
 
 

Advance Sumproduct calculation

Post by vezeri » Thu, 02 Nov 2006 05:28:45


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

Kostis
 
 
 

Advance Sumproduct calculation

Post by Harlan Gro » Thu, 02 Nov 2006 05:31:11

Brad wrote...
...

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)
 
 
 

Advance Sumproduct calculation

Post by QnJhZ » Thu, 02 Nov 2006 05:36:01

I'm using version 2003
 
 
 

Advance Sumproduct calculation

Post by QnJhZ » Thu, 02 Nov 2006 06:32:02

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