» Calculating the Average Value in Every Nth Column, Excluding Zeros
CATEGORY - Excel Array Formulas
VERSION - All Microsoft Excel Versions
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))}
Book Store:
Recommended Books:
- Business Analysis with Microsoft Excel (2nd Edition)
- Understanding Financial Statements
- Special Edition Using Microsoft Word 2002
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Special Edition Using Microsoft Outlook 2002
- Marketing Plan: A Handbook with Marketing Plan
No comments have been submitted.
