Ask Your Question

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]

asked 2017-03-17 20:55:39 +0200

aceking370 gravatar image

updated 2020-07-08 20:35:36 +0200

Alex Kemp gravatar image

Spread sheet

edit retag flag offensive 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

2 Answers

Sort by » oldest newest most voted

answered 2017-03-17 21:19:07 +0200

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

Your column A isn't required for the task.

edit flag offensive delete link more


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

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

answered 2017-03-18 00:50:58 +0200

LKeithJordan gravatar image

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.

edit flag offensive delete link more


why simple if it could be done sophisticated !?

karolus gravatar imagekarolus ( 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.

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

Question Tools

1 follower


Asked: 2017-03-17 20:55:39 +0200

Seen: 51 times

Last updated: Mar 18 '17