Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Formula
I am looking for a formula that will allow me to apply one multiplier up to a certain sum and then a different multiplier after it hits that limit. For example, I have a column of numbers 1, 3, 5, 7, 10. For the sum of the numbers until 10, I need to apply a multiplier of 2. All sums after 10, I need to apply a multiplier of 10. In this case, I would like the responding column to read 2 (2*1), 6(2*3), 10(2*5), 62(2*1+6*10), 100 (10*10) Thanks. -- ATK ------------------------------------------------------------------------ ATK's Profile: http://www.excelforum.com/member.php...o&userid=27662 View this thread: http://www.excelforum.com/showthread...hreadid=476252 |
#2
|
|||
|
|||
Here is a shot
=IF(A1*210,A1*10,A1*2) it doesn't work for the 7 in your example, but that one doesn't seem to correspond to your description. -- HTH RP (remove nothere from the email address if mailing direct) "ATK" wrote in message ... I am looking for a formula that will allow me to apply one multiplier up to a certain sum and then a different multiplier after it hits that limit. For example, I have a column of numbers 1, 3, 5, 7, 10. For the sum of the numbers until 10, I need to apply a multiplier of 2. All sums after 10, I need to apply a multiplier of 10. In this case, I would like the responding column to read 2 (2*1), 6(2*3), 10(2*5), 62(2*1+6*10), 100 (10*10) Thanks. -- ATK ------------------------------------------------------------------------ ATK's Profile: http://www.excelforum.com/member.php...o&userid=27662 View this thread: http://www.excelforum.com/showthread...hreadid=476252 |
#3
|
|||
|
|||
=IF(SUM($A$1:A1)<10,A1*2,A1*10) Assumes your data is in column A1:Axx, formula entered in B1 and copied down. I am guessing you want to sum the range from A1 down to the cell containing the active formula. e.g. in cell B5, the formula will read: =IF(SUM($A$1:A5)<10,A5*2,A5*10) and with your sample data will return: 100 However, B4 will return 70, not 62 (2*1+6*10), (how did you arrive at that with a vaule of 7?) Is this what you are looking for? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=476252 |
#4
|
|||
|
|||
Hi
Your description doesn't match your example. Going by the example, I guess you multiply by 2 for any value up to and equal to 5, any value between 6 and 9, is 6*10 plus (Value-6)*2 and any value of 10 and higher multiply by 10. If so =IF(A1=10,A1*10,IF(A1=6,(6*10+(A1-6)*2),A1*2)) Regards Roger Govier ATK wrote: I am looking for a formula that will allow me to apply one multiplier up to a certain sum and then a different multiplier after it hits that limit. For example, I have a column of numbers 1, 3, 5, 7, 10. For the sum of the numbers until 10, I need to apply a multiplier of 2. All sums after 10, I need to apply a multiplier of 10. In this case, I would like the responding column to read 2 (2*1), 6(2*3), 10(2*5), 62(2*1+6*10), 100 (10*10) Thanks. |
#5
|
|||
|
|||
Hi
Your description doesn't match your example. Going by the example, I guess you multiply by 2 for any value up to and equal to 5, any value between 6 and 9, is 6*10 plus (Value-6)*2 and any value of 10 and higher multiply by 10. If so =IF(A1=10,A1*10,IF(A1=6,(6*10+(A1-6)*2),A1*2)) Regards Roger Govier ATK wrote: I am looking for a formula that will allow me to apply one multiplier up to a certain sum and then a different multiplier after it hits that limit. For example, I have a column of numbers 1, 3, 5, 7, 10. For the sum of the numbers until 10, I need to apply a multiplier of 2. All sums after 10, I need to apply a multiplier of 10. In this case, I would like the responding column to read 2 (2*1), 6(2*3), 10(2*5), 62(2*1+6*10), 100 (10*10) Thanks. |
#6
|
|||
|
|||
Excel Formula
I guess my description was a little ambiguous. When I say sum of the numbers, I mean the sum of the numbers in the column. So, for the resulting value of 62, the sum of the first three numbers is 9. The forth number in the column is 7. So, 1 gets the multiplier of 2 and 6 gets a multiplier of 10 -- 2*1+6*10=62. Make more sense now? -- ATK ------------------------------------------------------------------------ ATK's Profile: http://www.excelforum.com/member.php...o&userid=27662 View this thread: http://www.excelforum.com/showthread...hreadid=476252 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Excel show a formula in ONE cell ? | Excel Discussion (Misc queries) | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) | |||
Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions |