# 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? [closed]

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-07-08 20:35:50.313691

Sort by » oldest newest most voted

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

more

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

( 2017-03-18 00:28:45 +0200 )edit

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.

more

why simple if it could be done sophisticated !?

( 2017-03-18 01:03:19 +0200 )edit

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.

( 2017-03-18 02:21:51 +0200 )edit

## Stats

Seen: 51 times

Last updated: Mar 18 '17