Tip Printed from ExcelTip.com

---

Calculating the Average Value in Every Nth Column, Excluding Zeros


Problem:

Row 2 contains numeric values, with their matching serial numbers shown in row 1.
We want to calculate an average of the values from every 3rd column, excluding zeros from the calculation.

Solution:

Use the AVERAGE, IF, MOD, COLUMN, and CELL functions in the following Array formula:
{=AVERAGE(IF((MOD(COLUMN(B2:K2)-CELL("col",B2)+1,E4)=0)*(B2:K2<>0),B2:K2))}