Calculate the highest 5 consecutive year average salary

HI Monid78,

Try using below formula:-

Formula:

=AVERAGE(LARGE($B2:$K2,{1,2,3,4,5}))

see attached:-
Attachment 211457

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

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>

You mean like this perhaps?

Formula:

=MAX(AVERAGE(B2:F2),AVERAGE(C2:G2),AVERAGE(D2:H2),AVERAGE(E2:I2),AVERAGE(F2:J2),AVERAGE(G2:K2))

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

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

90000 75000 10000 15000 80000
54,000 average

Try using below formula:-

Formula:

=AVERAGE(OFFSET(A1,0,MATCH(MAX(A1:J1),A1:J1,0)-1,1,5))

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>

still formula will work, see attached:-


highest consecutives calculation.xlsx


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

still formula will work, see attached:-


Attachment 211938


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

cheers

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



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

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 ?

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>

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>

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>

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

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.

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

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.

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

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

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

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

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

Old thread but somehow I got entangled in this.

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

Option Explicit
Dim m As Integer, n As Integer, cd As Integer, k As Integer
Dim sum As Double
Function AverageLast(Rng As Range, LatestRight As Boolean, NoOfInAverage As Integer)

n = 0
m = 0
If LatestRight Then
    cd = Rng.Cells.Count
    k = 1
Else
    cd = 1
    k = -1
End If
While n < NoOfInAverage And m < Rng.Cells.Count And m >= -Rng.Cells.Count
    If Len(Rng.Cells(cd - m).Value) > 0 Then
        sum = sum + Rng.Cells(cd - m).Value
        n = n + 1
    End If
    m = m + k
Wend
AverageLast = sum / NoOfInAverage
End Function

FYI Jacc,

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

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