Ask Your Question
0

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?

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

aceking370 gravatar image

Spread sheet

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

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

Comments

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

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

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

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

Comments

why simple if it could be done sophisticated !?

karolus gravatar imagekarolus ( 2017-03-18 01:03:19 +0100 )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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 40 times

Last updated: Mar 18 '17