| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula which multiplies by two seperate %'s
Hi all,
I need to create a formula which multiplies the first $52,000 by 2% and anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for any help, Byron |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
try
=val * 0.02 + max(val-52000,0)*0.02 "Dos Equis" wrote: Hi all, I need to create a formula which multiplies the first $52,000 by 2% and anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for any help, Byron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
one way
=(MIN(F1,50000)*0.02)+(MAX(F1-50000,0)*0.04) -- Don Guillett SalesAid Software "Dos Equis" wrote in message oups.com... Hi all, I need to create a formula which multiplies the first $52,000 by 2% and anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for any help, Byron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
=52000*.02+(X-52000)*.04 where X is your amount above $52,000.
Dave -- Brevity is the soul of wit. "Dos Equis" wrote: Hi all, I need to create a formula which multiplies the first $52,000 by 2% and anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for any help, Byron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
Maybe that should be:
=(MIN(F1,52000)*0.02)+(MAX(F1-52000,0)*0.04) HTH, -- Kevin James. Tua'r Goleuni "Don Guillett" wrote in message ... | one way | =(MIN(F1,50000)*0.02)+(MAX(F1-50000,0)*0.04) | | -- | Don Guillett | SalesAid Software | | "Dos Equis" wrote in message | oups.com... | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | | | |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
That only works for numbers greater than or equal to 52000.
Less than 52000 and the second half of the formula results in a negative value. HTH, -- Kevin James. Tua'r Goleuni "Dave F" wrote in message ... | =52000*.02+(X-52000)*.04 where X is your amount above $52,000. | | Dave | -- | Brevity is the soul of wit. | | | "Dos Equis" wrote: | | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | | |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
Slightly modified version
=( val+MAX(val-52000,0))*0.02 HTH, -- Kevin James. Tua'r Goleuni "bj" wrote in message ... | try | =val * 0.02 + max(val-52000,0)*0.02 | | "Dos Equis" wrote: | | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | | |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
Hi Dos Equis,
Perhaps, =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) Where B5 is the value you set the limit at i.e. 52000 Where B7 is the value to be evaluated. HTH, -- Kevin James. Tua'r Goleuni "Dos Equis" wrote in message oups.com... | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
Thanks for all the help, while I was waiting I found an effective if
more involved soloution that involves two cells with the following formulas: =(IF(C5<=52000,C5*0.02,0)) and =(IF(C5=52000,(C5-52000)*0.04,0)). I then hid the cells and life was good. When I first started on this I was told the formula was a quarterly report, now I find out that it is a quarterly report, but the amount is cumulative, meaning that at the end of the first quarter the total may be in the 25,000 range, second quarter around 50,000, third 75K and 4th is above that... 52,000 is the cut off and may happen in May or September... So, I now need to keep a running total and switch from 2% to 4% when 52,000 is reached. I think the formula =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) may have merit as it is already pointed to another cell for validation. Please help some more... TIA Byron Ponty'NPop wrote: Hi Dos Equis, Perhaps, =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) Where B5 is the value you set the limit at i.e. 52000 Where B7 is the value to be evaluated. HTH, -- Kevin James. Tua'r Goleuni "Dos Equis" wrote in message oups.com... | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
Sorry for the confusion, it would help if I had recieved the proper
information to begin with. I'll try to clarify here. Legal ads are tracked over the course of a year. For the first $52,000 charged, the legal secretary receives a 2% bonus, for everything $52,001 and above, she receives a 4% bonus. the bonus is paid quarterly and is being tracked in excel 2000. I was able to make it work using 4 seperate formulas, each very simular to one another. This one is the most involved and does work, but was a pain to create. =IF(C5+G5+K5<=52000,K5*0.02,IF(C5+G5+K552000,((C5 +G5+K5)-52000)*0.04+(52000-(C5+G5))*0.02)) Monthly totals are in columns C,G,K & O with quarterly totals on row 5. the formula above is from the third quarter as I simply took the quarterly total of the 4th quarter and assumed 4%. Basicly, I summed the first 3 quarters and compared that to 52K, if less than, then sumX2% if greater than 52K then (Sum - 52K) * 4% + (52K - (q1+q2)) * 2% this way, she gets credit for everything. Not sure about effciency, but it works and that was my goal. Thanks for the help provided, once I saw some of the suggestions, I was able to work this up. Thanks all, Byron Don Guillett wrote: I am confused. Originally you said that 2% was paid on the 1st 52,000 and and extra 2% paid of the amount over 52,000...... Or, is 4% paid on all after 52,000 is reached? Which??? Now, how often is the commission paid? Each quarter_________? Each month. When??? Jan-Mar 40,000 = 2% Apr-Jun 23,000 = 2% +2% of 11,000 or 2% of 63,000?? =(F1+MAX(F1-52000,0))*0.02 seems to be the most efficient formula It does help to properly ask the question in the beginning. -- Don Guillett SalesAid Software "Dos Equis" wrote in message oups.com... Thanks for all the help, while I was waiting I found an effective if more involved soloution that involves two cells with the following formulas: =(IF(C5<=52000,C5*0.02,0)) and =(IF(C5=52000,(C5-52000)*0.04,0)). I then hid the cells and life was good. When I first started on this I was told the formula was a quarterly report, now I find out that it is a quarterly report, but the amount is cumulative, meaning that at the end of the first quarter the total may be in the 25,000 range, second quarter around 50,000, third 75K and 4th is above that... 52,000 is the cut off and may happen in May or September... So, I now need to keep a running total and switch from 2% to 4% when 52,000 is reached. I think the formula =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) may have merit as it is already pointed to another cell for validation. Please help some more... TIA Byron Ponty'NPop wrote: Hi Dos Equis, Perhaps, =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) Where B5 is the value you set the limit at i.e. 52000 Where B7 is the value to be evaluated. HTH, -- Kevin James. Tua'r Goleuni "Dos Equis" wrote in message oups.com... | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula which multiplies by two seperate %'s
Sorry for the confusion, it would help if I had recieved the proper
information to begin with. I'll try to clarify here. Legal ads are tracked over the course of a year. For the first $52,000 charged, the legal secretary receives a 2% bonus, for everything $52,001 and above, she receives a 4% bonus. the bonus is paid quarterly and is being tracked in excel 2000. I was able to make it work using 4 seperate formulas, each very simular to one another. This one is the most involved and does work, but was a pain to create. =IF(C5+G5+K5<=52000,K5*0.02,IF(C5+G5+K552000,((C5 +G5+K5)-52000)*0.04+(52000-(C5+G5))*0.02)) Monthly totals are in columns C,G,K & O with quarterly totals on row 5. the formula above is from the third quarter as I simply took the quarterly total of the 4th quarter and assumed 4%. Basicly, I summed the first 3 quarters and compared that to 52K, if less than, then sumX2% if greater than 52K then (Sum - 52K) * 4% + (52K - (q1+q2)) * 2% this way, she gets credit for everything. Not sure about effciency, but it works and that was my goal. Thanks for the help provided, once I saw some of the suggestions, I was able to work this up. Thanks all, Byron Don Guillett wrote: I am confused. Originally you said that 2% was paid on the 1st 52,000 and and extra 2% paid of the amount over 52,000...... Or, is 4% paid on all after 52,000 is reached? Which??? Now, how often is the commission paid? Each quarter_________? Each month. When??? Jan-Mar 40,000 = 2% Apr-Jun 23,000 = 2% +2% of 11,000 or 2% of 63,000?? =(F1+MAX(F1-52000,0))*0.02 seems to be the most efficient formula It does help to properly ask the question in the beginning. -- Don Guillett SalesAid Software "Dos Equis" wrote in message oups.com... Thanks for all the help, while I was waiting I found an effective if more involved soloution that involves two cells with the following formulas: =(IF(C5<=52000,C5*0.02,0)) and =(IF(C5=52000,(C5-52000)*0.04,0)). I then hid the cells and life was good. When I first started on this I was told the formula was a quarterly report, now I find out that it is a quarterly report, but the amount is cumulative, meaning that at the end of the first quarter the total may be in the 25,000 range, second quarter around 50,000, third 75K and 4th is above that... 52,000 is the cut off and may happen in May or September... So, I now need to keep a running total and switch from 2% to 4% when 52,000 is reached. I think the formula =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) may have merit as it is already pointed to another cell for validation. Please help some more... TIA Byron Ponty'NPop wrote: Hi Dos Equis, Perhaps, =((B7=B5)*B5+(B7<B5)*B7)*0.02+((B7B5)*(B7-B5)*0.04) Where B5 is the value you set the limit at i.e. 52000 Where B7 is the value to be evaluated. HTH, -- Kevin James. Tua'r Goleuni "Dos Equis" wrote in message oups.com... | Hi all, | | I need to create a formula which multiplies the first $52,000 by 2% and | anything left by 4%. Sounds easy, but It's kicking my butt. Thanks for | any help, | | Byron | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Creating a complicated formula | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Creating Formula using check boxes | Excel Discussion (Misc queries) | |||
| Formula to Seperate data in 1 cell | Excel Worksheet Functions | |||
Linear Mode
