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

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),C OLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1),5)))

"Michael" wrote:

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

How about this

=SUM(J1:INDEX(A1:J1,IF(ISERROR(LARGE(IF(A1:J1<0,C OLUMN(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)



"Michael" wrote in message
...
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),C OLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1),5)))

"Michael" wrote:

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

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,C 1: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)

--
Biff
Microsoft Excel MVP


"Michael" wrote in message
...
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