Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel function
I am try to do the following: (i.e....)
Col A Col.B Col C. Col. D Col E col. F $50,000 $2,000 $2500 $1,000 $5,000 ??? if the sum of columns c through e is less than col A and greater than Col. B times 3, then return col. b times 3. if not, return col a minus sum of column c through one cell before Col f Also, if sum of c thru e is greater than or equal to col a, then reutrn 0 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel function
=IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2 )B2*3),B2*3,SUM(C2:E2)))
I think -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Candentco Creative Collection" <Candentco Creative wrote in message ... I am try to do the following: (i.e....) Col A Col.B Col C. Col. D Col E col. F $50,000 $2,000 $2500 $1,000 $5,000 ??? if the sum of columns c through e is less than col A and greater than Col. B times 3, then return col. b times 3. if not, return col a minus sum of column c through one cell before Col f Also, if sum of c thru e is greater than or equal to col a, then reutrn 0 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel function
"Bob Phillips" wrote...
=IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E 2)B2*3), B2*3,SUM(C2:E2))) .... I suspect your first SUM term is a typo and you meant =IF(SUM(C2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2 )B2*3), B2*3,SUM(C2:E2))) However, I don't think that's what the OP requested. My take on it is =IF(SUM($C2:E$2)=$A2,0,IF(SUM($C2:$E3)3*$B2,3*$B 2,$A2-SUM($C2:$E2))) with the column-absolute references due to the OP's 'sum of column c through one cell before Col f', which leads me to suspect the formula would go into F2 then be filled right. I've seen residual amounts spread in similar manner before. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel function
Bob, thanks for the effort and turning on the light.
Harlan, thank you, sir, for the eclipse! You were right on!!! "Harlan Grove" wrote: "Bob Phillips" wrote... =IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E 2)B2*3), B2*3,SUM(C2:E2))) .... I suspect your first SUM term is a typo and you meant =IF(SUM(C2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2 )B2*3), B2*3,SUM(C2:E2))) However, I don't think that's what the OP requested. My take on it is =IF(SUM($C2:E$2)=$A2,0,IF(SUM($C2:$E3)3*$B2,3*$B 2,$A2-SUM($C2:$E2))) with the column-absolute references due to the OP's 'sum of column c through one cell before Col f', which leads me to suspect the formula would go into F2 then be filled right. I've seen residual amounts spread in similar manner before. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel function
Again I come to the tank for a solution...
When I copy the following formula: =ROUND(IF(SUM($I17:I$17)=$F17,0,IF($F17-(SUM($I17:I$17))3*$H17,3*$H17,$F17-SUM($I17:I$17))),0) down one cell, the result is: =ROUND(IF(SUM($I$17:I18)=$F18,0,IF($F18-(SUM($I$17:I18))3*$H18,3*$H18,$F18-SUM($I$17:I18))),0) when my goal is: =ROUND(IF(SUM($I18:I$18)=$F18,0,IF($F18-(SUM($I18:I$18))3*$H18,3*$H18,$F18-SUM($I18:I$18))),0) The area of issue is the ($I18:I$18) which repeats another two times in the formula. Why does it change where the $ signs are? ...and is there a way to accomplish by objective here? Molti grazie! "Harlan Grove" wrote: "Bob Phillips" wrote... =IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E 2)B2*3), B2*3,SUM(C2:E2))) .... I suspect your first SUM term is a typo and you meant =IF(SUM(C2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2 )B2*3), B2*3,SUM(C2:E2))) However, I don't think that's what the OP requested. My take on it is =IF(SUM($C2:E$2)=$A2,0,IF(SUM($C2:$E3)3*$B2,3*$B 2,$A2-SUM($C2:$E2))) with the column-absolute references due to the OP's 'sum of column c through one cell before Col f', which leads me to suspect the formula would go into F2 then be filled right. I've seen residual amounts spread in similar manner before. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel function
Sorry to waste everyone's valuable perusing time, but I solved my latest
mystery with the following: =ROUND(IF(SUM($I17:I17)=$F17,0,IF($F17-(SUM($I17:I17))3*$H17,3*$H17,$F17-SUM($I17:I17))),0) Have a good one! -- MVD San Francisco, California "Candentco Creative Collection" wrote: Again I come to the tank for a solution... When I copy the following formula: =ROUND(IF(SUM($I17:I$17)=$F17,0,IF($F17-(SUM($I17:I$17))3*$H17,3*$H17,$F17-SUM($I17:I$17))),0) down one cell, the result is: =ROUND(IF(SUM($I$17:I18)=$F18,0,IF($F18-(SUM($I$17:I18))3*$H18,3*$H18,$F18-SUM($I$17:I18))),0) when my goal is: =ROUND(IF(SUM($I18:I$18)=$F18,0,IF($F18-(SUM($I18:I$18))3*$H18,3*$H18,$F18-SUM($I18:I$18))),0) The area of issue is the ($I18:I$18) which repeats another two times in the formula. Why does it change where the $ signs are? ...and is there a way to accomplish by objective here? Molti grazie! "Harlan Grove" wrote: "Bob Phillips" wrote... =IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E 2)B2*3), B2*3,SUM(C2:E2))) .... I suspect your first SUM term is a typo and you meant =IF(SUM(C2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2 )B2*3), B2*3,SUM(C2:E2))) However, I don't think that's what the OP requested. My take on it is =IF(SUM($C2:E$2)=$A2,0,IF(SUM($C2:$E3)3*$B2,3*$B 2,$A2-SUM($C2:$E2))) with the column-absolute references due to the OP's 'sum of column c through one cell before Col f', which leads me to suspect the formula would go into F2 then be filled right. I've seen residual amounts spread in similar manner before. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
challenge! javascript function into excel function | Excel Worksheet Functions | |||
FUNCTION GETPIVOTDATA MICROSOFT EXCEL 2003 VS EXCEL 2004 FOR MAC | Excel Worksheet Functions | |||
Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
Using the MSNStockQuote.function excel 2002 how do excel 2003 | Excel Discussion (Misc queries) | |||
Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions |