Creating a formula which multiplies by two seperate %'s

D

Dos Equis

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
 
P

Ponty'NPop

Maybe that should be:

=(MIN(F1,52000)*0.02)+(MAX(F1-52000,0)*0.04)

HTH,
--
Kevin James.
Tua'r Goleuni
| one way
| =(MIN(F1,50000)*0.02)+(MAX(F1-50000,0)*0.04)
|
| --
| Don Guillett
| SalesAid Software
| [email protected]
| | > 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
| >
|
|
 
P

Ponty'NPop

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


| =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
| >
| >
 
P

Ponty'NPop

Hi Dos Equis,

Perhaps,

=((B7>=B5)*B5+(B7<B5)*B7)*0.02+((B7>B5)*(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


| 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
|
 
D

Dos Equis

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+((B7>B5)*(B7-B5)*0.04) may have merit
as it is already pointed to another cell for validation. Please help
some more...

TIA

Byron
 
D

Don Guillett

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.
 
D

Dos Equis

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+K5>52000,((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
 
D

Dos Equis

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+K5>52000,((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
 
Top