Calculate the highest 5 consecutive year average salary

[monid78]

I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate in excel Example.xlsfor each (if they have less than 5 years with salary, then it should just average the years the do have).
Some people have breaks in service (for five years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
Could someone please help me? Thank you.

[dilipandey]

HI Monid78,

Try using below formula:-

Please Login or Register  to view this content.

see attached:-
Attachment 211457

Regards,
DILIPandey
<click on below * if this helps>

[monid78]

Thank you so much. I think I missed telling one thing, it needs to be 5 highest consecutive years within the last 10 years.

[dilipandey]

5 highest consecutive years within the last 10 years

what if the highest years are not consecutive.. like:-

30000, 50000, 15000, 20000, 90000, 75000, 10000, 15000,80000,10000

now which all value (consecutives) you would consider ?

Regards,
DILIPandey
<click on below * if this helps>

[Jacc]

You mean like this perhaps?

Please Login or Register  to view this content.

I tried to make it as an array formula but it was too much for me.

[FDibbins]

Monid, please do not post duplicate threads. I have closed your other thread regarding this question, you may continue on this 1

[monid78]

90000 75000 10000 15000 80000
54,000 average

[monid78]

Thank you so much Jacc. I think that worked.

[dilipandey]

90000 75000 10000 15000 80000
54,000 average

Try using below formula:-

Please Login or Register  to view this content.

see attached:- highest consecutives calculation.xlsx

Only issue you'll face when there are less than 5 numbers available adjacent to highest number (90,000 in this case)

Regards,
DILIPandey
<click on below * if this helps>

[monid78]

I will have quite a few employees who will have less than 5 years. If an employee has worked only two years earning 50,000 and 30,000, his average would be 40,000 in that case. What would you propose?

[dilipandey]

still formula will work, see attached:-


highest consecutives calculation.xlsx


Regards,
DILIPandey
<click on below * if this helps>

[dilipandey]

still formula will work, see attached:-


Attachment 211938


Regards,
DILIPandey
<click on below * if this helps>

[monid78]

Yes, it does. Thank you so much.

[dilipandey]

cheers

You are welcome Monid78. Suggest you to mark this thread as [SOLVED]. Thanks.



Regards,
DILIPandey
<click on below * if this helps>

[Jacc]

I must admit that I'm a bit confused here. You seem to approve both mine and DILIPandey's solutions, yet they are not the same at all.
I thought the idea was to calculate the average for all possible 5 year periods within the range and the take the maximum of that.
DILIPandey's solution is calculating the average during a 5 year period, starting with the highest value in the series. If the highest value is the last, then it will include 4 cells outside the data.
By coincidence, these two different formulas yield the same result in the example given in post 7.

I may be voted of this planet for arguing when everyone already seems content, but so be it. This seems too much like a missunderstanding.
What result would you expect from the data series:
1 1 1 1 1 1 1 1 1 16 ?

[monid78]

You are right Jacc. It is misunderstanding. DILIPandey's solution is not working if the highest salary is at the end. I am not proficient is excel formulas at all, obviously. I tried a few examples and by coincidence it worked. Anyway, I would expect average of 4 from this series. Sorry to both of you

[dilipandey]

I also noticed this Jacc and hence mentioned in my previous post #9 :-

Only issue you'll face when there are less than 5 numbers available adjacent to highest number (90,000 in this case)

on right side.. off course - missed that


and thats why we say "Requirement" step in a solution development (SDLC) is equally important


Regards,
DILIPandey
<click on below * if this helps>

[monid78]

Is there one formula (or possibly 2-3 separate formulas) that can consider all possible options? I have over 4000 employees that I need to calculate the average for and it might be difficult to go through each entry manually. Your help is greatly appreciated.

[dilipandey]

You need to decide your stand on each possible combinations, one could be :-

90000,50000,40000,20000,91000,67000,99999,55000

highest 5 consecutives you want, but what if they are not together ... where to start then ?






Regards,
DILIPandey
<click on below * if this helps>

[monid78]

Example.xls
I have attached an example. The key word is consecutive. You can't jump around between 10 years to find the highest values.

[dilipandey]

okay.. why your example 1 has 5 figures in each row ?? I dont think this will be your real scenario.


Regards,
DILIPandey
<click on below * if this helps>

[Jacc]

monid78, does that mean that my formula from post #5 is basically correct?

[monid78]

Example 1 is simply one employee with 10 salaries. I wanted to ilustarte which salaries I would consider for my average.

[monid78]

Example 3.xlsJacc, going through a few examples, it seems your formula doesn't work if I have 5 or less salary information. See attached.

[Jacc]

Ok, now it works but it looks terrible.
dilipandey, do you think you can make this formula more compact? like making it into an arrayformula or something.
I tried to make it into an arrayformula several times before but I can't get it to work.

[daddylonglegs]

Try this version

=IFERROR(MAX(SUBTOTAL(1,OFFSET(B2,0,COLUMN(B2:G2)-COLUMN(B2),1,5))),AVERAGE(B2:K2))

confirmed with CTRL+SHIFT+ENTER

[Jacc]

Now we're talkin'! An array formula that is completely incomprehensible.
Seems to work perfectly, now I'll have to spend the rest of the night decode this thing.

[monid78]

Example 3 Jacc.xlsI used your sheet and added a few examples. Can you see why this doesn't work? Thank you Jacc

[monid78]

Example 3 daddylonglegs.xlsThansk daddylonglegs. I tested it and a few examples didn't work. Why?

[Jacc]

The problem is that the formula ignores the empty cells. If the empy cells are filled in with zeros it seems to calculate correctly.

[monid78]

The formula should ignore empty cells, otherwise it counts "0" towards the average if there are less than 5 years in data.

[daddylonglegs]

So what result do you expect if fewer than 5 cells are filled, e.g. B2:D2 only are filled - do you just average those 3?

Assuming the answer is yes then try this version

=IF(COUNT(B2:K2)<5,AVERAGE(B2:K2),MAX(SUBTOTAL(9,OFFSET(B2,0,COLUMN(B2:G2)-COLUMN(B2),1,5))/5))

confirmed with CTRL+SHIFT+ENTER

[monid78]

Yes. And sometimes happens that employee comes back after a few years of break. For example employee worked 2012, 2011 and 2005. I would count all three years for the average. To sum up, I am looking for highest-earning 5 consecutive calendar years of employment within the last 10 years. If there are less than 5 years, I average what is available. Hope this make sense. Thank you so much for all help. This is way bigger for me than I thought.

[daddylonglegs]

I edited my previous reply to include a formula suggestion - does that work for you?

[monid78]

Example 3 daddylonglegs-NEW FORM.xls
Thank you, but this new formula still doesn't work. Please see attached.

[daddylonglegs]

You haven't "array entered" those formulas.

Put the first formula in row 2 then select the cell with the formula and press F2 key to select formula. Now hold down CTRL and SHIFT keys and at the same time press ENTER. You will see curly braces like { and } appear around the formula in the formula bar and the results should be as expected.....

Now you can copy the formula down the column

[monid78]

Thank you. I think that worked. I greatly appreciate it. I never heard of "array entered". What does this mean?

[monid78]

It turned out the formula daddylonglegs helped me with doesn't work. For the last month I was working on reviewing records for 3000 of our employees. Just recently I have calculated salaries and turned out that the formula doesn't work for all. Please see attached.
It looks the formula averages salary 1 through 5 with salaries 4 and 5 being at 0. It should ignore empty cells and move on to next salary.
I would appreciate your help. Thank you. Example 1.xls

[Jacc]

Old thread but somehow I got entangled in this.

This UDF seems to work if the latest 5 is either from left or right:

Please Login or Register  to view this content.

[daddylonglegs]

FYI Jacc,

This question was continued here and I eventually managed to suggest a formula which seemed to work.......

[Jacc]

In case someone stumbles on this in the future and decides to use it;
add sum=0 above n=0.