| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct calculation between two workbooks | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Hybrid Mode
