Hi All:
I’m trying to create a formula for a bonus payout. The formula needs to take into account 2 payout events and also incorporate a payout factor.
Event A is worth up to $1500. Event B is worth up to $900. Each bonus is paid based on the completed percentage of the event. If the combined percentage is 100% or higher, the total payout is multiplied by a factor of 1. If the combine percentage is less, so is the multiplication factor.
For example if event A was completed 80% and event B was completed 50%, their combined completion would be 130%. That would make them eligible for the highest multiplication factor (1), which is paid on $1200 for event A (1500 * .8) and $450 (900*.5). The end bonus is $1650 (1200+450)*1.0.
If event A was completed 50% and event B was completed 20%, their combined completion would be 70%. That would make them eligible for a lower multiplication factor (0.9), which is paid on $750 for event A (1500 * .5) and $180 (900*.2). The end bonus is $837 (750+180)*.9
Here’s the English version of the formula I’m trying to write.
If E6 is greater than 1, multiply C6 by I6 and add it to the sum of D6 *J6, If E6 is greater than 0.75 but less than 1, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .95. If E6 is greater than 0.49 but less than 0.75, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .90. If E6 is greater than 0.24 but less than 0.49, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .85. If E6 is less than 0.24, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .80.
The first part of my SUMIF formula worked, but as I tried to add to it, it fell apart….
=SUMIF(E6,">1",D6)*J6+C6*I6
Thank in advance.
I’m trying to create a formula for a bonus payout. The formula needs to take into account 2 payout events and also incorporate a payout factor.
Event A is worth up to $1500. Event B is worth up to $900. Each bonus is paid based on the completed percentage of the event. If the combined percentage is 100% or higher, the total payout is multiplied by a factor of 1. If the combine percentage is less, so is the multiplication factor.
For example if event A was completed 80% and event B was completed 50%, their combined completion would be 130%. That would make them eligible for the highest multiplication factor (1), which is paid on $1200 for event A (1500 * .8) and $450 (900*.5). The end bonus is $1650 (1200+450)*1.0.
If event A was completed 50% and event B was completed 20%, their combined completion would be 70%. That would make them eligible for a lower multiplication factor (0.9), which is paid on $750 for event A (1500 * .5) and $180 (900*.2). The end bonus is $837 (750+180)*.9
Here’s the English version of the formula I’m trying to write.
If E6 is greater than 1, multiply C6 by I6 and add it to the sum of D6 *J6, If E6 is greater than 0.75 but less than 1, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .95. If E6 is greater than 0.49 but less than 0.75, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .90. If E6 is greater than 0.24 but less than 0.49, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .85. If E6 is less than 0.24, multiply C6 by I6 and add it to the sum of D6 *J6 THEN multiply that by .80.
The first part of my SUMIF formula worked, but as I tried to add to it, it fell apart….
=SUMIF(E6,">1",D6)*J6+C6*I6
Thank in advance.
