Calculating the average over the last 5 values.

**spitfireking** · 11-11-2011, 12:57 PM

I am looking for a formula to calculate the average for each row of numbers but to only pick the last 5 values (but to ignor 0).

So the averages of the below rows
A 22 22 26 24 0 0 21 59 74 68
B 100 98 100 0 90 100 94 95 91 49
C 56 0 0 0 0 0 52 95 41 28
D 47 80 0 50 0 47 0 95 0 97

As time goes on there will be more data added these (in the spreadsheet 0's will be blank) so the formula will need to average the last 5 data points from the right of the row.

**Colin Legg** · 11-11-2011, 01:11 PM

Welcome to the forum.

Taking row A as an example, I'd just like to clarify a point about ignoring the zeros.

Which of these do you want to be the average?

(24 + 21 + 59 + 74 + 68) / 5

or

(21 + 59 + 74 + 68) / 4

Assuming you want the first of these variations, this array formula (CTRL+SHIFT+ENTER) should do the trick:

=AVERAGE(INDEX(3:3,,LARGE(ISNUMBER(3:3)*COLUMN(3:3),5)):IV3)

**SDBoca** · 11-11-2011, 04:09 PM

I tried the formula but am getting a DIV/0 error. Do you need both commas after 3:3? what does IV3 refer to?

Thanks,

Sharon

**Colin Legg** · 11-11-2011, 04:24 PM

Hi,

Yes, you need both commas
IV3 refers to the last cell in the column (based on Excel 2003).
The formula I gave was for row 3; you have to adjust the row references for the other rows.

You would get a DIV/0 error if the row is empty.

I have attached example.