Average the last eight or nine (Variable) numbers in a row.

[Michael]

I need to be able to average the last numbers in a row but there are a few
problems. 1. New numbers are added to each row each week. 2. Sometimes those
numbers are zero which I don't need to count. I must average the last eight
weeks of employees pay not counting certian weeks that are represented by the
zero. Each week, there pay is added to the row so I would need to add that
week to the average. A1 is the employee's name, B1 is where the average will
go, C1 thru IV1 is the gross pay for each week.

750, 825, 0, 915, 850, 775, 0, 885, 925, 0, 745, 875

If the numbers above represented the numbers in the columns C1 thru N1, I
would need to average the 875, 745, 925, 885, 775, 850, 915 & 825. Next week
when the pay is added in cell O1, I would need to include that in the average
and take the 825 out of the average.

I'm not were I will be able to try your suggestions right away. The company
I work for does not provide internet access and I can't bring the file home
to work on it. But I would really appreciate any suggestions that you might
have and I'll let you know tomorrow how it works.

Thanks so much

 

[Michael]

The following formula from an earlier post works fine for me except for one
thing, I don't have blank spaces between my numbers, I have zero's. Can this
be changed to not count the zero's?

=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(ISNUMBER(A1:J1),COLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1),5)))

 

[Bob Phillips]

How about this

=SUM(J1:INDEX(A1:J1,IF(ISERROR(LARGE(IF(A1:J1<>0,COLUMN(A1:J1)),5)),COUNTIF(A1:J1,"<>0"),LARGE(IF(A1:J1<>0,COLUMN(A1:J1)),5))))/MIN(5,COUNTIF(A1:J1,"<>0"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(COLUMN(C1:IV1)>=LARGE(IF(C1:IV1,COLUMN(C1:IV1)),MIN(COUNTIF(C1:IV1,">0"),8)),IF(C1:IV1,C1:IV1)))

If there aren't 8 values to average it'll average what's available. If *all*
entries are 0 or there are *no* entries then the formula will return an
error.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)