AVERAGEing non contiguous rows
 

Hi,

I have a spreadsheet where the rows are labelled in a repeating sequence
Mon, Tue, Wed, Thu, Fri - the first row (Mon) is 118 and the last row (Fri)
is 1007. There are then a number of columns (C thru T) with numeric values
relating to each respective weekday. I want an expression that will allow me
to average the non zero entries for each weekday. Having read other posts, I
tried
=AVERAGE(IF(MOD(ROW(C$118:C$1007),5)=0,IF(C$118:C$ 10070,C$118:C$1007))) but
the results don't look right - can anyone spot where I'm going wrong?

AVERAGEing non contiguous rows
 

PS. I entered the formula with Ctrl + Shift + Enter

"Mike McLellan" wrote:

Hi,

I have a spreadsheet where the rows are labelled in a repeating sequence
Mon, Tue, Wed, Thu, Fri - the first row (Mon) is 118 and the last row (Fri)
is 1007. There are then a number of columns (C thru T) with numeric values
relating to each respective weekday. I want an expression that will allow me
to average the non zero entries for each weekday. Having read other posts, I
tried
=AVERAGE(IF(MOD(ROW(C$118:C$1007),5)=0,IF(C$118:C$ 10070,C$118:C$1007))) but
the results don't look right - can anyone spot where I'm going wrong?

AVERAGEing non contiguous rows
 

Not sure what result you expect but your formula will average those cells
where the row is divisible by 5: C120, C125, C130, C135, C140 etc and the
value is 0.

--
Biff
Microsoft Excel MVP


"Mike McLellan" wrote in message
...
Hi,

I have a spreadsheet where the rows are labelled in a repeating sequence
Mon, Tue, Wed, Thu, Fri - the first row (Mon) is 118 and the last row
(Fri)
is 1007. There are then a number of columns (C thru T) with numeric
values
relating to each respective weekday. I want an expression that will allow
me
to average the non zero entries for each weekday. Having read other
posts, I
tried
=AVERAGE(IF(MOD(ROW(C$118:C$1007),5)=0,IF(C$118:C$ 10070,C$118:C$1007)))
but
the results don't look right - can anyone spot where I'm going wrong?