ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Advance Sumproduct calculation (https://www.excelbanter.com/excel-worksheet-functions/116879-advance-sumproduct-calculation.html)

Brad
Advance Sumproduct calculation
 
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
Advance Sumproduct calculation
 
=SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21)

HTH
Kostis Vezerides


Brad wrote:
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.

Brad
Advance Sumproduct calculation
 
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" wrote:

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

HTH
Kostis Vezerides


Brad wrote:
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
Advance Sumproduct calculation
 
I reproduced the conditions and it worked fine. Are you getting a
result?


Brad wrote:
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" wrote:

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

HTH
Kostis Vezerides


Brad wrote:
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.

Brad
Advance Sumproduct calculation
 
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" wrote:

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


Brad wrote:
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" wrote:

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

HTH
Kostis Vezerides


Brad wrote:
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
Advance Sumproduct calculation
 
I don't see a reason why... As I said I reproduced the condition
exactly. Anyway, hope someone jumps in.

Kostis


Brad wrote:
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" wrote:

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


Brad wrote:
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" wrote:

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

HTH
Kostis Vezerides


Brad wrote:
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.

Harlan Grove
Advance Sumproduct calculation
 
Brad wrote...
No - it is giving me a value error
....
My guess is it doesn't like the if

Correct.

"vezerid" wrote:
I reproduced the conditions and it worked fine. Are you getting a
result?
....

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

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

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)

Brad
Advance Sumproduct calculation
 
I'm using version 2003

"vezerid" wrote:

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

Kostis


Brad wrote:
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" wrote:

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


Brad wrote:
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" wrote:

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

HTH
Kostis Vezerides


Brad wrote:
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.

Brad
Advance Sumproduct calculation
 
Found that that this was entered as an array equation - that part I missed

"Harlan Grove" wrote:

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

Correct.

"vezerid" wrote:
I reproduced the conditions and it worked fine. Are you getting a
result?
....

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

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

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)


All times are GMT +1. The time now is 11:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com