# Excel: bonus disqualifiers for excess vacation days

hope all is well everyone. may i seek your help for coming up with a template for computing for disqualifiers for a certain bonus given the following conditions (that sadly i cannot convert to formula): folks are given bonus for completing work days in a month; however, due to being understaffed, taking vacation leaves that go beyond 10% of work days in a month is discouraged. base bonus amount = 2,903.336 (subject to change monthly but is fixed for this specific example) # of worked days in a month = 21 (other possible values of days worked: 19, 20, 22, 23) 10% of days worked 19 = 1.9 20 = 2 21 = 2.1 22 = 2.2 23 = 2.3 # days in excess of 10% of work days will incur increasing deductions 1 day = less 25% of base amount 2 days = less 50% of base amount 3 days = less 75% of base amount 4 days = less 100% of base amount for example, month of june has 21 work days. an employee with exactly 2 (to at most 2.1) days approved & plotted vacation, will be rewarded 100% of base bonus amount whereas an employee with who filed for 4 days of leaves (4/21 = 19% has .9 excess days is definitely more than 10% of 21 work days of the month), will still be rewarded bonus but 50% less of base bonus amount compared to the other employee in prior example. kindly see sample table below. for results shown in the attachment, these were just manually computed & referenced to tables. any approach or presentation to make it even leaner/better, will be greatly appreciated.

``````

A
B
C
D
E
F
G

A1
base bonus amount
2,903.336

A2

A3
# of work days
10%

excess days
deduction
amount
total

A4
19
1.9

1
-25%
-725.834
2,177.502

A5
20
2

2
-50%
-1451.668
1,451.668

A6
21
2.1

3
-75%
-2177.502
725.834

A7
22
2.2

4
-100%
-2903.336
0.000

A8
23
2.3

A9

A10

A11
Agent
Leave(s)
bonus

A12
Agent 01
0
2,903.336

A13
Agent 02
0
2,903.336

A14
Agent 03
2
2,903.336

A15
Agent 04
6
0

A16
Agent 05
1
2,903.336

A17
Agent 06
4
1451.668

A18
Agent 07
4
1451.668

A19
Agent 08
3
2177.502

A20
Agent 09
3
2177.502

A21
Agent 10
5
725.834

``````
looking forward to your prompt help. cheers.