Average offset

Question

Level 1

4 points

Average offset

Hi,

I am trying to average the last 10 values in a column containing more than 10 values and I want the formula to update this automatically as more entries are entered.

My data starts from cell D2 and continues to D100 (not all filled in yet). The formula I am using is:

=AVERAGE(OFFSET(D1,COUNT(D:D),0,−10))

The error message I receive is "Argument 4 in OFFSET must be greater than or equal to 1 and less than or equal to 65,535".

I assume the -10 in the formula is the issue but not sure why.

Can anyone help?

Much obliged.

iPad Pro 11-inch Wi-Fi, Cellular

Posted on Dec 28, 2018 9:39 AM

Reply

Answer 1

Top-ranking reply

jaxjason

Level 5

5,063 points

Dec 28, 2018 12:21 PM in response to fazthfc

you were close with your formula. (I cant check in numbers right now, but in excel it is basically the below)

=AVERAGE(OFFSET(B1 , COUNTA(B)-10 , 0 , 10 , 1))

you need to offset by ten rows up in the second argument. then just set a height of ten rows for the fourth.

If you have less than ten values it will still throw an error. this also assumes that there is no header row, adjust accordingly.

This is actually the exact question i pose to people i interview for positions where excel knowledge is required, to better gauge what their true "how experienced are you" rating is ( this along with quite a few more and varied questions gives me a relative rating between candidates on knowledge)

Reply

Answer 2

Dec 28, 2018 11:04 AM in response to fazthfc

because you should not start this formula until row 11 (or later) because the offset would result in a negative row

Reply

Answer 3

jaxjason

Level 5

5,063 points

Dec 28, 2018 12:29 PM in response to jaxjason

just realized you were working in D, just replace B with D above obviously.

Also since your data starts in D2 headers arent an issue of course it will work just as is.

Reply