Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
Could someone please!!! help me to set up a complex formula which I will use
many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
Assuming your $7,417 value is in A1, I think this formula does what you
want... =0.667*MIN(A1,2500)+0.5*MIN(MIN(A1-2500,3500),6000)+0.4*MAX(A1-6000,0) -- Rick (MVP - Excel) "RENEE" wrote in message ... Could someone please!!! help me to set up a complex formula which I will use many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
You could take a look at www.mcgimpsey.com/excel/variablerate.html. In some ways, it is easier to maintain (change as needed), although I prefer to use VLOOKUP. Alternatively, try: =MIN(A1*66.7%, (A1-2500)*50%+1667.5, (A1-6000)*40%+3417.5) For dollars-and-cents results, it would be prudent to round that, viz.: =ROUND(MIN(A1*66.7%, (A1-2500)*50%+1667.5, (A1-6000)*40%+3417.5), 2) The number 6000 is the amount corresponding to "the next 3500"; that is, 2500+3500. The numbers 1667.5 and 3417.5 are the max amounts (tax?) corresponding to the previous bracket. You can bootstrap these amounts as follows. First, compute MIN(A1*66.7%) with A1=2500 (1667.5). Then compute MIN(A1*66.7%,(A1-2500)*50%+1667.5) with A1=6000 (3417.5). Etc. ----- original message ----- "RENEE" wrote: Could someone please!!! help me to set up a complex formula which I will use many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
Thanks, a little complex, but great.
"Joe User" wrote: You could take a look at www.mcgimpsey.com/excel/variablerate.html. In some ways, it is easier to maintain (change as needed), although I prefer to use VLOOKUP. Alternatively, try: =MIN(A1*66.7%, (A1-2500)*50%+1667.5, (A1-6000)*40%+3417.5) For dollars-and-cents results, it would be prudent to round that, viz.: =ROUND(MIN(A1*66.7%, (A1-2500)*50%+1667.5, (A1-6000)*40%+3417.5), 2) The number 6000 is the amount corresponding to "the next 3500"; that is, 2500+3500. The numbers 1667.5 and 3417.5 are the max amounts (tax?) corresponding to the previous bracket. You can bootstrap these amounts as follows. First, compute MIN(A1*66.7%) with A1=2500 (1667.5). Then compute MIN(A1*66.7%,(A1-2500)*50%+1667.5) with A1=6000 (3417.5). Etc. ----- original message ----- "RENEE" wrote: Could someone please!!! help me to set up a complex formula which I will use many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
Tried that one out and it works perfectly. Thanks so much!!!!
"Rick Rothstein" wrote: Assuming your $7,417 value is in A1, I think this formula does what you want... =0.667*MIN(A1,2500)+0.5*MIN(MIN(A1-2500,3500),6000)+0.4*MAX(A1-6000,0) -- Rick (MVP - Excel) "RENEE" wrote in message ... Could someone please!!! help me to set up a complex formula which I will use many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
On Wed, 28 Apr 2010 15:33:02 -0700, RENEE
wrote: Tried that one out and it works perfectly. Thanks so much!!!! Personally, I would not do this with an inline formula. I would write a little UDF. Then, instead of a complicated formula that mau not make any sense to you in 6 months, each cell would have something like: =VarPC(A1) You can put as much commentary as you like in the UDF and if you ever decide to change any of the parameters, you just change one function and all of the cells are updated. You can also add other arguments if you need any variability. "Rick Rothstein" wrote: Assuming your $7,417 value is in A1, I think this formula does what you want... =0.667*MIN(A1,2500)+0.5*MIN(MIN(A1-2500,3500),6000)+0.4*MAX(A1-6000,0) -- Rick (MVP - Excel) "RENEE" wrote in message ... Could someone please!!! help me to set up a complex formula which I will use many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
complex formula
"RENEE" wrote:
Tried that one out and it works perfectly. I don't think so. "Rick Rothstein" wrote: =0.667*MIN(A1,2500) +0.5*MIN(MIN(A1-2500,3500),6000) +0.4*MAX(A1-6000,0) The middle term looks suspicious. For any value in A1 less than 2500, MIN(MIN(A1-2500,3500),6000) returns a negative number. For example, test with A1=2000. The result should be 1334 (2000*66.7%). Rick's formula returns 1084, viz. 2000*66.7% + (-500*50%). We can correct Rick's formula, befitting his style, to wit: =0.667*MIN(A1,2500) +0.5*MAX(0,MIN(MIN(A1-2500,3500),6000)) +0.4*MAX(0,A1-6000) But the MIN(MIN(...)) construct seems superfluous: the inner MIN is no more than 3500, which is always less than 6000. (Note that if 3500 were replaced with a larger number X, 6000 would also be replaced with a larger number, 2500+X, which is always larger than X.) So Rick's corrected formula can be simplified to: =0.667*MIN(A1,2500) +0.5*MAX(0,MIN(A1-2500,3500)) +0.4*MAX(0,A1-6000) PS: You can continue to write 66.7%, 50% and 40% instead of the decimal fraction equivalents. ----- original message ----- "RENEE" wrote: Tried that one out and it works perfectly. Thanks so much!!!! "Rick Rothstein" wrote: Assuming your $7,417 value is in A1, I think this formula does what you want... =0.667*MIN(A1,2500)+0.5*MIN(MIN(A1-2500,3500),6000)+0.4*MAX(A1-6000,0) -- Rick (MVP - Excel) "RENEE" wrote in message ... Could someone please!!! help me to set up a complex formula which I will use many times over. I have an amount - say $7,417. I need to take 66.7% of the first $2,500 (which comes to $1,667.50). This leaves me with $4,917 of the original amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with $1,417 of the original number which I need to take 40% of ($566.80) Then, I add all the percentages up to come to $3,984.30. I have to do this a lot and could certainly use an easier way other than a calculator and a large sheet of paper. Thanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex formula help | Excel Discussion (Misc queries) | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Complex formula | Excel Worksheet Functions | |||
Complex Formula | Excel Discussion (Misc queries) | |||
Can someone help with this complex formula? | Excel Worksheet Functions |