Assume data in column A repeats the row number. Column B contains random data. What formula (in column C beyond row 10) would yield the average of the last 10 values in column B?

Spread sheet

Put this formula into C10: =AVERAGE(B1:B10), and drag-copy it downwards as required.

Your column A isn’t required for the task.

Thanks a ton. Simple and I was trying to make something complicated of it.

I’m taking this off the top of my head while tapping on a cell phone, so my answer may or may not be precisely correct – but it should give you the right idea. Here is one solution:

Assume your cursor is in cell C20. The last data is in cell B19. Here is a formula:

=AVERAGEIFS($B:$B, $A:$A, “>=$A10”, $A:$A, “<=$A19”)

By using the combination of relative and absolute cell address referencing, the formula should self-adjust if you copy it further down column C for additional averages.

Please click the check mark next to the response you believe best answers your question.

why simple if it could be done sophisticated !?

I interpreted your question as something to be re-used – not as a one shot deal. Apparently @aceking370 read your question correctly.

I suggest you give him a check mark to thank him. Good luck with your spreadsheet.