 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006Advance 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. =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) HTH Kostis Vezerides > Column C has numeric values > Column F has numeric values [quoted text clipped - 27 lines] > The number in column C will not always be constant but Column F will always > be the sum of column C. When I keyed this in and do formula evaluator - it does not like the first c10:c21 statement - it gives me a value error > =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) > [quoted text clipped - 32 lines] > > The number in column C will not always be constant but Column F will always > > be the sum of column C. I reproduced the conditions and it worked fine. Are you getting a result? > When I keyed this in and do formula evaluator - it does not like the first > c10:c21 statement - it gives me a value error [quoted text clipped - 35 lines] > > > The number in column C will not always be constant but Column F will always > > > be the sum of column C. 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 > I reproduced the conditions and it worked fine. Are you getting a > result? [quoted text clipped - 38 lines] > > > > The number in column C will not always be constant but Column F will always > > > > be the sum of column C. I don't see a reason why... As I said I reproduced the condition exactly. Anyway, hope someone jumps in. Kostis > No - it is giving me a value error > [quoted text clipped - 50 lines] > > > > > The number in column C will not always be constant but Column F will always > > > > > be the sum of column C. 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. [quoted text clipped - 55 lines] > > > > > > The number in column C will not always be constant but Column F will always > > > > > > be the sum of column C. Brad wrote... >No - it is giving me a value error ... >My guess is it doesn't like the if Correct. >>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. >>>>=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) Found that that this was entered as an array equation - that part I missed > Brad wrote... > >No - it is giving me a value error [quoted text clipped - 21 lines] > =SUMPRODUCT((C10:C21<1099.41-F10:F21)*C10:C21 > +(C10:C21>=1099.41-F10:F21)*(1099.41-F10:F21),M10:M21) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.