Average non contiguous row

[Scott Kieta]

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?

 

[Toppers]

First, these are non-contiguous columns not rows: second, are they alternate
D,F,H ,J etc?

 

[Scott Kieta]

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.

 

[Toppers]

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)

 

[Scott Kieta]

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

 

[Toppers]

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