Most valuable professional
  • Hot Topics

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. Code: A1 A2

This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of June 15, 2014.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel