Sum last 5 or less in a column

[robb7979]

Hello all, New here. Please be gentle, I tried the search function. What I need to do is average the last 6 or less values in a column b3:b13. I can't seem to figure it out "or less" please help!!

[Moo the Dog]

What determines how many values are averaged?

- Moo

[robb7979]

What determines how many values are averaged?

- Moo

It is a monthly input, but I only need the last 6 values or less averaged. So if 2 or more cells have a value average or if more than 6 have a value, then only the last 6. Thanks!

[Moo the Dog]

Try this in Excel 2003:
You must hit Ctrl+Shift+Enter instead of just Enter when you type in this formula (it's an array formula)

Please Login or Register  to view this content.

..or in Excel 2007:

Please Login or Register  to view this content.

* That will average the values in B8:B13. (Assuming the last 6 values would be in those cells) If the values appear anywhere in the range of B3:B13 then those formulas won't work.
-Moo

[Teethless mama]

Average the last 6 values

=AVERAGE(INDEX(A2:A1000,MATCH(10^10,A2:A1000)):INDEX(A2:A1000,MATCH(10^10,A2:A1000)-MIN(5,COUNT(A2:A1000))))

Adjust your range to suit

[robb7979]

Teethless, Thank you!! Do I click something that increases your Rep? Not sure of the proper etiquette.

[Moo the Dog]

Average the last 6 values

=AVERAGE(INDEX(A2:A1000,MATCH(10^10,A2:A1000)):INDEX(A2:A1000,MATCH(10^10,A2:A1000)-MIN(5,COUNT(A2:A1000))))

Adjust your range to suit

Teethless mama,

For some reason I tried that formula and I am getting the wrong result. See attached sheet. Did I input something incorrectly? I changed the formula to match the OP's range (B3:B13):

Please Login or Register  to view this content.

- Moo