# Finding the frequency AND location of consecutive months of underperformance

1. ## Finding the frequency AND location of consecutive months of underperformance

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!

2. ## Re: Finding the frequency AND location of consecutive months of underperformance

it would help if you could post a sample file as it's hard to discern your table structure / references but...

if we assumed, for illustrative purposes, that your Averages were in A3:A13 and Bonus in B3:B13 then, perhaps:

Formula:
the above would return the Bonus payment prior to the first sequence of 3 consecutive averages < threshold or, if no such sequences exist, the 840
(i.e. 840 for first set of values, 630 for second set etc...)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1