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(B1100%,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.
--
Texas Wannano

Try:

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

HTH

"Wannano" wrote:

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(B1100%,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.
--
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",B11),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.

"Wannano" wrote:

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(B1100%,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.
--
Texas Wannano

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!

"JLatham" wrote:

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",B11),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.

"Wannano" wrote:

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(B1100%,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.
--
Texas Wannano

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


"JLatham" wrote:

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",B11),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.

"Wannano" wrote:

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(B1100%,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.
--
Texas Wannano