# Thread: Get the average of the last 5 non-zero cells in a row (not column)

1. ## Get the average of the last 5 non-zero cells in a row (not column)

Objective: Get the average of the last 5 non-zero cells in a row (not column).
I am using Excel 2010.
I have a row of 15 cells with values in the first 10 cells, some of which are zeros.
I will be adding additional values (or zeros) each week for a total of 15 weeks.
I have done a lot of research and have discovered that there are many ways to accomplish this - far too many for me to investigate given my time constraints and low level of expertise.

Please, help me accomplish this. I would be most grateful.
Many thanks.

2. Hi,
plz see the file.

3. Hi,
what do you mean by "last"? the five lowest values?

4. ## Reply to hossat

Originally Posted by hossat
Hi,
what do you mean by "last"? the five lowest values?
Hello. By "last" I mean the most recently entered five numbers...going from left to right...disregarding the zero/blank cells that may be in some of the cells.

Example: 1 0 2 3 0 4 5 6 0 7 . . .

In this case I want the average of 3, 4, 5, 6, 7, which would be 25/5=5. The zeros should be ignored. Only the last five cells with values will be averaged. Any of the cells could have zeros or even left blank...whichever would be best for use in a formula.

Thanks for your help.

5. Hi,
in cell Q6 put the below array formula (Ctrl+Shift+Enter)
=SUM(P6:INDEX(B6:P6,LARGE((COLUMN(B6:P6)-MIN(COLUMN(B6:P6))+1)*(B6:P6<>0),5)))/5

6. Greetings sambit,
Your formula works grrrrrreat! I plan to learn it from the inside out.
Many, many thanks to you, my friend.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•