+ Reply to Thread
Results 1 to 2 of 2

Finding the frequency AND location of consecutive months of underperformance

  1. #1
    Registered User
    Join Date
    06-10-2020
    Location
    USA
    MS-Off Ver
    2018
    Posts
    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!
    Last edited by drose9500; 06-11-2020 at 10:06 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    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: copy to clipboard
    Please Login or Register  to view this content.
    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...)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding/counting consecutive # of values in a row - frequency function?
    By Elijah in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 10-27-2016, 05:38 PM
  2. Replies: 3
    Last Post: 01-21-2016, 02:23 PM
  3. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  4. [SOLVED] Consecutive Employee Vacation Days using sumif array with frequency and column functions
    By ciaran01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 10:24 AM
  5. Replies: 2
    Last Post: 01-18-2013, 06:06 PM
  6. [SOLVED] Frequency of months occuring
    By gbug1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-04-2012, 10:03 AM
  7. [SOLVED] i need to add consecutive months and keep the day the same for ea.
    By Jason in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-21-2005, 06:06 PM

Tags for this Thread

Bookmarks

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