Excel formula

[TamIam]

Hello there,

I was wondering if anyone could help me with a formula that will calculate
the following:

Commission Levels:
Target $2500
95% to 100% of Target $1250
For each additional 1% above 100% $50

e.g. if achieved %110 of target, then payout would be $2500 plus 10%
(10x$50) for a grand total of $3000 for payout...There is no cap to this
payout.

I have been racking my brains and have come up with the following formula
which works well unless the percentage ends in a zero:

=IF(AND(VALUE(E8)>=95%),IF(VALUE(E8)<101%,1250,RIGHT(ROUND(VALUE(E8),2),2)*50+2500),0)

 

[David Biddulph]

Try =1250*(E8>=95%)+1250*(E8>=100%)+50*100*MAX(0,E8-100%)

 

[Duke Carey]

Maybe:

=(E8>=.95)*(1250+Max(0,(e9-1)*100)*50)

 

[Don Guillett]

Can you give 2-3 examples of the correct answer for the amount given.

 

[Duke Carey]

By the way, your description and formula are inconsistent. You state:

95% to 100% of Target $1250

but your formula uses $2,500

 

[Duke Carey]

Make that

=(E8>=.95)*(1250+Max(0,(e8-1)*100)*50)

and if you really mean $2,500 instead of $1,250, then

=(E8>=.95)*(2500+Max(0,(e8-1)*100)*50)

 

[TamIam]

This works very well, however, is it possible to add a 'rounding' option say
to a percentage of 1.27300045? I only want to pay the $50 on the 27% over
target.

Again..many thanks!

 

[Duke Carey]

=(E8>=.95)*(1250+Max(0,(min(e8,1.27)-1)*100)*50)

 

[Roger Govier]

Hi Duke

I wonder if the OP meant a rounding of the % to decimal places, rather
than the specific 1.27?
If so maybe
=(E8>=0.95)*(1250+MAX(0,(ROUND(E8*100,0)/100-1)*100)*50)

 

[Roger Govier]

rounding of the % to decimal places
That should of course have read
rounding of the % to 2 decimal places