If And/Or statement for comm calc criteria

[Wannano]

This is to calculate a 2-part commission payment.

A1 Monthly Target = $1,000
A2 Performance % = 168%
A3 Payout up to 100% (100% of target - $1,000)
A4 Payout based of Performance % >100% up to 150% (2% of target for every %
above 100% of plan up to 150% of plan - otherwise, even if performance is
200%, the commission is capped at 150% performance)

I need a formula to calculate A4 - commission >100% up to 150% performance
===================================================
Results should be
A1 - $1,000 / A2 - 168% / A3 - $1,000 / A4 - $1,000

A5 - $2,000 Total Commission

 

[RagDyeR]

A3:
=A1*MIN(A2,1)

A4:
=2*(A2>1)*MIN(MOD(A2,1),0.5)*A1

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

This is to calculate a 2-part commission payment.

A1 Monthly Target = $1,000
A2 Performance % = 168%
A3 Payout up to 100% (100% of target - $1,000)
A4 Payout based of Performance % >100% up to 150% (2% of target for every %
above 100% of plan up to 150% of plan - otherwise, even if performance is
200%, the commission is capped at 150% performance)

I need a formula to calculate A4 - commission >100% up to 150% performance
===================================================
Results should be
A1 - $1,000 / A2 - 168% / A3 - $1,000 / A4 - $1,000

A5 - $2,000 Total Commission

 

[RagDyeR]

1 less function call for A4:

=2*(A2>1)*MIN(A2-1,0.5)*A1
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


A3:
=A1*MIN(A2,1)

A4:
=2*(A2>1)*MIN(MOD(A2,1),0.5)*A1

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

This is to calculate a 2-part commission payment.

A1 Monthly Target = $1,000
A2 Performance % = 168%
A3 Payout up to 100% (100% of target - $1,000)
A4 Payout based of Performance % >100% up to 150% (2% of target for every %
above 100% of plan up to 150% of plan - otherwise, even if performance is
200%, the commission is capped at 150% performance)

I need a formula to calculate A4 - commission >100% up to 150% performance
===================================================
Results should be
A1 - $1,000 / A2 - 168% / A3 - $1,000 / A4 - $1,000

A5 - $2,000 Total Commission

 

[Wannano]

That worked. Thanks!

 

[Ragdyer]

You're welcome and appreciate the feed-back.