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.

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

HTH

Kostis Vezerides

HTH

Kostis Vezerides

When I keyed this in and do formula evaluator - it does not like the first

c10:c21 statement - it gives me a value error

c10:c21 statement - it gives me a value error

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

result?

result?

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

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

I don't see a reason why... As I said I reproduced the condition

exactly. Anyway, hope someone jumps in.

Kostis

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)

...

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)

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

1. Size of workbook and calculation time huge because of Sumproducts

2. Advanced calculations in tables--Question 2

3. Help on Interest calculation? Advanced maths ?

5. Advanced calculations in tables--Question 1

6. Sumproduct instead of SumifS in VBA (application.sumproduct)

7. sumproduct? sumif(sumproduct)?

8. Copying a calculations answer and not the calculation

9. Calculation Field Comparing Calculation Fields?

10. Excel calculation engine for fast calculations

11. Calculations vs Fields defined as calculations

12. Determining calculation order for group of related calculations

13. Calculation on Calculation Field

14. Multi threaded calculation (multi CPU) - impact on calculation spe

15. range.calculation with UDF not working when calculation is set to automatic

9 post • Page:**1** of **1**