+ Reply to Thread
Results 1 to 5 of 5

n highest consecutive numbers out of m where n<m

  1. #1
    Registered User
    Join Date
    03-03-2011
    Location
    LA, USA
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    n highest consecutive numbers out of m where n<m

    Our summary plan description for a retirement plan has the following component:
    "Your Highest Five-Year Average Compensation is the average of your Compensation for the 60 highest paid consecutive months of employment during the last 120 months before retirement or termination. If you have less than 60 months of employment, compensation is averaged over your actual months of service from your date of hire until your termination."
    It sounds simple but what would be the excel formula to calculate the average assuming, A1:A200 contains the data series?

    Thanks in advance.

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: n highest consecutive numbers out of m where n<m

    Question: why would there be 200 rows of data for 120 months?
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Registered User
    Join Date
    03-03-2011
    Location
    LA, USA
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    Re: n highest consecutive numbers out of m where n<m

    Employment may be more that 120 months, I arbitrarily picked 200 months. Employment may be less than 60 months for some.
    Last edited by ExlLrnr; 03-04-2011 at 06:59 PM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: n highest consecutive numbers out of m where n<m

    Hi EliLrnr and welcome to the forum,

    Look at the attached to see if it works for you. In Col A is monthly compensation (I used a random to make it easy). Then in B is the average of last consecutive 60 months. Then in C is the count of consecutive months. Now for the cool part... DMAX is a Database Maximum of the averages only if the count is 60. Finally a formula if there are no 60 consecutive months then just average the whole range.

    I think this is what you want.
    Attached Files Attached Files
    Last edited by MarvinP; 03-04-2011 at 07:52 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    03-03-2011
    Location
    LA, USA
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    Re: n highest consecutive numbers out of m where n<m

    Hi MarvinP,

    Thank you for introducing me to the DMAX function and thank you for the worksheet you provided so quickly. I will have to play with it to evalute it.
    Best regards,

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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