Finding average of last three entries

on the 'Stats' tab of the attached sample the formula in M3 is designed to display the average of the last two numbers entered in cells P3-AAD3. this was supplied to me by someone on this site, and it works exactly as needed. However, our parameters have changed and now I need M3 to display the average of the last three numbers instead. I've tried to change the formula but it results in an error. The error remains even if I try to change the formula back.
I would like not only a solution but a bit of a tutorial on the formula used (or a source to find out how it works on my own). Any help is greatly appreciated.

sample.xlsx


Brian

Try this:

Formula:

Please Login or Register  to view this content.

This is an array formula, it has to be entered with Ctrl + Shift + Enter. That's the reason it stopped working for you even if you didn't change it, you have to finish it of with Ctrl + Shif + Enter any time you edited it. If you just click in the formula bar and then hit Esc it should be fine but if you hit Enter you ruined it. Then you have to click in the formula bar again and then hit Ctrl + Shift + Enter. If done properly you will see the { } surrounding the formula, it indicates that is now an array formula. You can't enter those brackets manually.

The original formula was set up as an Arrayed formula which is why it's not working any more. I would suggest this substitute (This is not an arrayed formula)
=AVERAGE(OFFSET(P3,,MATCH(1E+100,P3:AAD3)-3,1,3))

P3 is the OFFSET Starting point,

,, is how many rows you want to offset =0

MATCH(1E+100,P3:AAD3)-3 is the number of columns you want to move over to a new starting point.

The MATCH is looking for 100000000000.... (100 zeros) when it doesn't find it, it matches the last number it finds then we subtract 3 from that to get a starting point which is third from the end.

,1 is the number of rows you want to include in your result
3 is the width or # of columns you want included in your result.
Does that help?

=AVERAGE(INDEX(3:3,MATCH(10^99,$O3:$Aad33,1)+COLUMN(N$1)-2):INDEX(3:3,MATCH(10^99,$O3:$Aad3,1)+COLUMN(N$1)))
will give the average of the last 3

Thanks!
Both the first two work fine. I will probably go with the one that isn't an array formula for now. Is there a way to modify this to use a variable to determine how many to average?

=AVERAGE(OFFSET(P3,,MATCH(1E+100,P3:AAD3)-A1,1,3)) where a1 contains the number of last cells you want to average

Fantastic!
Thank you again!!

Martin forgot one of the 3's

=AVERAGE(OFFSET(P3,,MATCH(1E+100,P3:AAD3)-A1,1,A1))