bonus for meeting target ranges

K

kiki burgh

New Member
Joined
Jun 9, 2014
Messages
44
hi! i am once again reaching out for support on coming up with a table with formulas to present for the following:
  • choose from specific range of values to identify specific skill
  • compute for # of those who passed & those who did not
  • compute for bonus 1 (those passed will get 10% of the allocation)
  • compute for bonus 2 (any excess amount (due to not everyone meeting the target) will be redistributed equally to those who passed
appreciate your support.


A
B
C
D
E
F
G
H
I
J
1
Allocation
Headcount
Success %
LOB
Skill
Target
Measure
Range
Bonus
2






Upper Limit
Lower Limit
3
8,750.00
Eligible =
Consumer
723
405.00
seconds
405.00
375.00
0.10






435.00
406.00
0.05
4
0.10
Not Eligible =
Small-Medium Scale
738
630.00
seconds
630.00
600.00
0.10






700.00
631.00
0.05
5
875.00

Enterprise
755
655.00
seconds
655.00
620.00
0.10






685.00
656.00
0.05
6










7

ID
Associate
Team Lead
Skill
AHT
Amount with Bonus 1
Amount with Bonus 2


8

1234567
Fordham, Julia
Lucas, George
755
633.000



9

1234568
Nicholson, Jack
McMahon, Vince
738
645.000



10

1234569
Wade, Dwayne
Ortiz, Tito
723
398.000



11

1234570
Johnson, Dwayne
Shamrock, Ken
723
406.000



12

1234571
Gates, Bill
Couture, Randy
738
610.000



13

1234572
Spielberg, Steven
Couture, Randy
738
627.000



14

1234573
Skywalker, Luke
Shamrock, Ken
738
720.000



15

1234574
Shakur, Tupac
Lucas, George
723
480.000



16

1234575
Jobs, Steve
McMahon, Vince
723
370.000



17

1234576
Perez, George
Ortiz, Tito
723
801.000



18

1234577
Wolvman, Marv
Shamrock, Ken
755
801.000



19

1234578
Bolton, Michael
Lucas, George
755
801.000




<tbody>
</tbody>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Your requests don't make much sense, without an example of what you actully want as an outcome.
How about giving us a worked example to go on with ??
 
Upvote 0
thank u for looking into this michael m. apologies but i thought all's given in my 1st post.
i have edited my 1st post to make it more specific. (oops, i thought i could still edit but i couldn't.)
thank u.
 
Upvote 0
yeah, sorry... I must be having one of my Seniors days, I still don't get it ...:oops:..
 
Upvote 0
apologies. my 10 minutes to edit expired. i hope additional information below helps:

thank u for looking into this michael m. apologies but i thought all's given in my 1st post.
i have edited my 1st post to make it more specific. (oops, i thought i could still edit but i couldn't. posted it below again.)
thank u.

  • cells a1:j8 are only notes (or extra headers); data table starts from b10:h22
  • choose from specific range of values to identify specific skill (skills are under column e: 723, 738 & 75)
  • compute for # of those who passed & those who did not (b3: eligible & b5: not eligible with answers to c3 & c5, respectively; manually computed there are only 5 hitting the target for 10% bonus 1 + 2 hitting the lower tier for 5%, thus answers for c3 = 47% (7/15) while for c5 = 53% (8/15))
  • compute for g10:g21 amount with bonus 1 (those passed based on column f: target will get 10% of the allocation input in a3, which is 8,750.00; manually the only passers are for each skill are: 723: 2+1; 738: 2+1; 755: 1. manually computed, total amount with bonus 1 = 5,250)
  • compute for h10:h21 amount with bonus 2 (any excess amount (due to not everyone meeting the target) will be redistributed equally to those who passed. manually computed, 8,750.00 - 5,250 = 3,500 surplus to be divided equally to 7 passers = additional 500 each. thus, h11 = 1,375.00; h12 = 937.50; h13 = 1,375.00; h14 = 937.50; h15 = 1,375.00; h16 = 1,375.00; & h19 = 1,375.00 under column h amount with bonus 2.
appreciate your support. i am hoping you can supply the formula to automate the computation.
A B C D E F G H I J
1 Allocation Headcount Success % LOB Skill Target Measure Range Bonus
2 Upper Limit Lower Limit
3 8,750.00 Eligible = Consumer 723 405.00 seconds 405.00 375.00 0.10
4435.00 406.00 0.05
5
0.10 Not Eligible = Small-Medium Scale 738 630.00 seconds 630.00 600.00 0.10
6700.00 631.00 0.05
5 875.00 Enterprise 755 655.00 seconds 655.00 620.00 0.10
7685.00 656.00 0.05
8
9
ID Associate Team Lead Skill AHT Amount with Bonus 1 Amount with Bonus 2
101234567 Fordham, Julia Lucas, George 755 633.000
11
1234568 Nicholson, Jack McMahon, Vince 738 645.000
12
1234569 Wade, Dwayne Ortiz, Tito 723 398.000
13
1234570 Johnson, Dwayne Shamrock, Ken 723 406.000
14
1234571 Gates, Bill Couture, Randy 738 610.000
15
1234572 Spielberg, Steven Couture, Randy 738 627.000
16
1234573 Skywalker, Luke Shamrock, Ken 738 720.000
17
1234574 Shakur, Tupac Lucas, George 723 480.000
18
1234575 Jobs, Steve McMahon, Vince 723 370.000
19
1234576 Perez, George Ortiz, Tito 723 801.000
20
1234577 Wolvman, Marv Shamrock, Ken 755 801.000
21 1234578 Bolton, Michael Lucas, George 755 801.000

<tbody>
</tbody>
 
Upvote 0
I think your manual calcs may be wrong, as there are only 12 participants, not 15....unless I have this completely wrong



Excel 2007
ABCDEFGHIJ
1AllocationHeadcountSuccess %LOBSkillTargetMeasureRangeBonus
2Upper LimitLower Limit
38750Eligible =58%Consumer723405seconds40537510%
44354065%
510%Not Eligible =42%Small-Medium Scale738630seconds63060010%
67006315%
7875Enterprise755655seconds65562010%
86856565%
9
10IDAssociateTeam LeadSkillAHTAmount with Bonus 1Amount with Bonus 2
111234567Fordham, JuliaLucas, George75563300
121234568Nicholson, JackMcMahon, Vince7386458751250
131234569Wade, DwayneOrtiz, Tito72339800
141234570Johnson, DwayneShamrock, Ken7234068751250
151234571Gates, BillCouture, Randy73861000
161234572Spielberg, StevenCouture, Randy73862700
171234573Skywalker, LukeShamrock, Ken7387208751250
181234574Shakur, TupacLucas, George7234808751250
191234575Jobs, SteveMcMahon, Vince72337000
201234576Perez, GeorgeOrtiz, Tito7238018751250
211234577Wolvman, MarvShamrock, Ken7558018751250
221234578Bolton, MichaelLucas, George7558018751250
23
2461258750
Sheet1
Cell Formulas
RangeFormula
C3=(SUMPRODUCT(--($E$11:$E$22=723)*($F$11:$F$22>$H$3))+SUMPRODUCT(--($E$11:$E$22=738)*($F$11:$F$22>$H$5))+SUMPRODUCT(--($E$11:$E$22=755)*($F$11:$F$22>$H$7)))/COUNT($E$11:$E$22)
C5=1-C3
A7=A3*A5
G11=IF(F11>VLOOKUP(E11,$E$3:$F$7,2,0),$A$7,0)
G12=IF(F12>VLOOKUP(E12,$E$3:$F$7,2,0),$A$7,0)
G13=IF(F13>VLOOKUP(E13,$E$3:$F$7,2,0),$A$7,0)
G14=IF(F14>VLOOKUP(E14,$E$3:$F$7,2,0),$A$7,0)
G15=IF(F15>VLOOKUP(E15,$E$3:$F$7,2,0),$A$7,0)
G16=IF(F16>VLOOKUP(E16,$E$3:$F$7,2,0),$A$7,0)
G17=IF(F17>VLOOKUP(E17,$E$3:$F$7,2,0),$A$7,0)
G18=IF(F18>VLOOKUP(E18,$E$3:$F$7,2,0),$A$7,0)
G19=IF(F19>VLOOKUP(E19,$E$3:$F$7,2,0),$A$7,0)
G20=IF(F20>VLOOKUP(E20,$E$3:$F$7,2,0),$A$7,0)
G21=IF(F21>VLOOKUP(E21,$E$3:$F$7,2,0),$A$7,0)
G22=IF(F22>VLOOKUP(E22,$E$3:$F$7,2,0),$A$7,0)
G24=SUM(G11:G23)
H11=IF(G11>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G11
H12=IF(G12>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G12
H13=IF(G13>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G13
H14=IF(G14>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G14
H15=IF(G15>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G15
H16=IF(G16>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G16
H17=IF(G17>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G17
H18=IF(G18>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G18
H19=IF(G19>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G19
H20=IF(G20>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G20
H21=IF(G21>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G21
H22=IF(G22>0,(($A$3-$G$24)/COUNTIF($G$11:$G$22,">"&0)),0)+G22
H24=SUM(H11:H23)
 
Upvote 0
oops ... must be out of confusion already by looking at so many numbers.
this is greatly appreciated mr. michael m.
 
Upvote 0
kiki burgh said:
oops ... must be out of confusion already by looking at so many numbers.
this is greatly appreciated mr. michael m.
Click to expand...
i will export this to a worksheet & i will try the formulas & will provide feedback.
your help & patience is greatly appreciated.
 
Upvote 0
my sincerest apologies mr. m. after trying it, most (if not all) under columns G (amount with bonus 1) & H (amount with bonus 2) have incorrect answers due to potentially incorrect range. the table below has correct manually computed results but still interested in a corrected version.
thank you again.

AHTAmount with Bonus 1Amount with Bonus 2
633.000875.001,375.00
645.000437.50937.50
398.000875.001,375.00
406.000437.50937.50
610.000875.001,375.00
627.000875.001,375.00
720.0000.000.00
480.0000.000.00
370.000875.001,375.00
801.0000.000.00
801.0000.000.00
801.0000.000.00
5,250.008,750.00
3,500.00
7.00
500.00

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

looking forward to a more updated worksheet. thanks.
 
Upvote 0
I'm afraid I'll have to stay confused....maybe someone else can offer a better solution....I'm ready to sleep !!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,216,207
Messages
6,129,508
Members
449,512
Latest member
Wabd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top