NEED HELP! Commission calculation

[Guest]

I need to write a commission formula that will calculate a cell, but if the
amount
calculated is greater than 750, than cap it at the 750.

For example

A1 = Center type (Full or Partial) only Full is qualified for this calculation
B1 = Percentage attained (0 up)
B3 = Payout up to 100% (say 500 which would be the base target payout)
B4 = Payout up to 150, with a cap of 150% of B3

I need help writing a formula for cell B4. Cell B3 is if(B1>100%,500,0)

For B4 calculation, the person must be in a "Full" center and will be paid
anything in Cell B1 OVER 100% with a cap at 150% of target (B3).

ALL assistance is very much appreciated.

 

[Guest]

Try:

=IF(A1="Full",MIN(B3*B1,B3*1.5),0)

HTH

 

[Guest]

Slightly different than what Toppers offered - not sure which is correct.
You should be able to determine which of us has what you need (or if we both
misinterpreted your needs)
=IF(AND(A1="Full",B1>1),MIN(MIN(B1-1,1.5)*B3,1.5*B3),0)
With Full in A1, 125% in B1, 500 in B3, I get $125, Topper's gives $625.

 

[Guest]

Yes .... not being an expert on commission calculations , I realised (when
thinking about mine) that your solution was an alternative; hopefully one of
us is right!

 

[Guest]

It worked...THANK YOU both!
--
Texas Wannano

Slightly different than what Toppers offered - not sure which is correct.
You should be able to determine which of us has what you need (or if we both
misinterpreted your needs)
=IF(AND(A1="Full",B1>1),MIN(MIN(B1-1,1.5)*B3,1.5*B3),0)
With Full in A1, 125% in B1, 500 in B3, I get $125, Topper's gives $625.