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),COLUMN(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,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)



"Michael" <(E-Mail Removed)> wrote in message
news:501C70AA-DDD3-4231-A146-(E-Mail Removed)...
> 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)))
>
> "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,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)

--
Biff
Microsoft Excel MVP


"Michael" <(E-Mail Removed)> wrote in message
news:69802480-97E5-4191-B1D2-(E-Mail Removed)...
>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