Average non contiguous row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was looking to do an average in non contigious rows using time but ignoring
zeros, so I used formula:
=Average(if((D17,F17<>0),(D17,F17),"")) but comes up #VALUE and when
utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell
h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for
non contigiuos row?
 
First, these are non-contiguous columns not rows: second, are they alternate
D,F,H ,J etc?
 
You are right these are columns, and yes these are alternate columns. The
main goal is to make sure the formula omits those zero values when doing the
average calculation.
 
try:

=AVERAGE(IF(MOD(COLUMN($D$3:$M$3),2)=0,IF($D$3:$M$3>0,$D$3:$M$3)))

Change ranges to suit

Enter with Ctrl+shift +Enter (array formula)
 
This works, excellent, thank you for your help. I have never used the MOD or
COLUMN functions before, if you don't mind can you give a short response on
those uses?

Thank You
 
COLUMN returns the column number so column D=4, E=5 etc.

The MOD function returns a remainder after the division by the divisor, in
this case 2. So all EVEN colums will have a remainder of 0 (if we divide by
2) whereas ODD columns will have a remainder of 1.

HTH
 
Back
Top