Calculating Employee Bonuses - Looking back 12 periods

[Jax11]

Hi all,

Looking for an elegant solution to calculate employee bonuses

I want to calculate Employee bonuses based on:
(1) previous 12 months salary payments (say annual salary is $120,000, but actual payments depend on the start date, say April)
(2) an input which dictates which calendar month the bonus is paid (say September)
(3) a percentage of that past 12 month's salary (say 10%)

The sheet is set up with each date being a column, and the salary and bonus amounts shown in a row for each employee.

See the example sheet below:

For Year 1, the appropriate salary months are 4-9 ($60k) with the bonus being $6k
For Year 2, the appropriate salary months are 10-21 ($120k) with bonus being $12k

Desired solution

I'm looking for a formula in Row 5 that looks up the relevant salary information for the previous 12 months, and calculates the salary piece.

CHALLENGE #1 - I want to have the same formula in every column (not a situation where the formula references the 12 previous periods and is dragged across the columns).
CHALLENGE #2 - By being able to choose the bonus month, the relevant 12 month period moves, so pinning the formula to a calendar year doesnt work


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]ROW1[/TD]
[TD]Month Date[/TD]
[TD]Jan'14[/TD]
[TD]Feb'14[/TD]
[TD]Mar'14[/TD]
[TD]Apr'14[/TD]
[TD]May'14[/TD]
[TD]Jun'14[/TD]
[TD]Jul'14[/TD]
[TD]Aug'14[/TD]
[TD]Sep'14[/TD]
[TD]Oct'14[/TD]
[TD]Nov'14[/TD]
[TD]Dec'14[/TD]
[TD]Jan'15[/TD]
[TD]Feb'15[/TD]
[TD]Mar'15[/TD]
[TD]Apr'15[/TD]
[TD]May'15[/TD]
[TD]Jun'15[/TD]
[TD]Jul'15[/TD]
[TD]Aug'15[/TD]
[TD]Sep'15[/TD]
[TD]Oct'15[/TD]
[TD]Nov'15[/TD]
[TD]Dec'15[/TD]
[/TR]
[TR]
[TD]ROW2[/TD]
[TD]Month Number[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]ROW3[/TD]
[TD]Monthly Salary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]ROW4[/TD]
[TD]Salary - YTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]120,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW5[/TD]
[TD]Bonus - 10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Looking forward to your expert opinions!!

Thankyou

 

[RonB1111]

1st: Make sure your dates in Row 1 are in "date" format (ie: they cannot be "text" format which they appear to be now).
2nd: In Cell A5, enter just the bonus percentage number so that it is a numeric value (ie: don't type in the word "Bonus -", type in a numeric amount only such as .1 or 10% or whatever % the bonus will be)
3rd: In Cell A6, enter the calendar month number (ie: 1 or 2 or 3 ... or 12) that the bonus will be paid. You can change this number and the results in the formula's below will automatically change.

Now, in Cell B4, enter this formula and copy across:
=IF(MONTH(B1)=$A6,SUM($B3:B3)-SUM($A4:A4),"")

Then in Cell B5, enter this formula and copy across:
=IF(B4="","",B4*$A5)