Hi all, excel VBA layman here. Currently faced with the challenge of scanning a list of an 11 month period for 3 or more consecutive months of under performance for an incentives spreadsheet.
There is accumulated bonus associated with meeting an average every month. For example; a consecutive 11 months of reaching the average goal of $625K nets you the top tier bonus of $840.
Billing Month Average Bonus
2 Months avge 625K $625,000.00 $420.00
3 Months avge 625K $913,945.33 $460.00
4 Months avge 625K $716,709.00 $505.00
5 Months avge 625K $773,367.20 $545.00
6 Months avge 625K $811,139.33 $590.00
7 Months avge 625K $838,119.43 $630.00
8 Months avge 625K $858,354.50 $670.00
9 Months avge 625K $846,315.11 $715.00
10 Months avge 625K $836,683.60 $755.00
11 Months avge 625K $911,683.60 $800.00
12 Months avge 625K $986,683.60 $840.00
However, you are penalized if there is ever a window in that 12 month span where you did not reach your goal for 3 or more consecutive months. Should that be the case, the bonus you receive should be the last month you reached the goal before the streak of under performing months. The below case should net you a bonus of $630, even though you reached your goal for the last two months of the period.
2 Months avge 625K $625,000.00 $420.00
3 Months avge 625K $913,945.33 $460.00
4 Months avge 625K $716,709.00 $505.00
5 Months avge 625K $773,367.20 $545.00
6 Months avge 625K $811,139.33 $590.00
7 Months avge 625K $698,119.43 $630.00
8 Months avge 625K $613,354.50 $670.00
9 Months avge 625K $547,426.22 $715.00
10 Months avge 625K $567,683.60 $755.00
11 Months avge 625K $642,683.60 $800.00
12 Months avge 625K $717,683.60 $840.00
I currently have something working 80% of the time with built in excel functions to show HOW MANY times you had a streak of 3 under performing months. But nothing to show where these occurrences happened in the 12 month span.
'=SUM(INT(FREQUENCY(IF(Twelve_Mo._Average < Average,COLUMN(Twelve_Mo._Average)),IF(Twelve_Mo._Average > Average,COLUMN(Twelve_Mo._Average)))/3))
I've tried everything I can think of to solve the problem, helper tables, a Power Query, but given the amount of cases where consecutive goals might not be met, I'm not sure if there is a solution that doesn't involve any sort of VBA, which I've never really touched.
If anyone could provide a little guidance, I'd be more than grateful!
Bookmarks