» Calculating the Average Value in Every Nth Column, Excluding Zeros
CATEGORY - Excel Array Formulas
VERSION - All Microsoft Excel Versions
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:
- Guide to Financial Reporting and Analysis
- The One Page Business Plan: Start With a Vision, Build a Company!
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- The Financial Numbers Game: Detecting Creative Accounting Practices
- Accounting Principles, with CD, 6th Edition
- Accounting and Financial Fundamentals for Nonfinancial Executives
No comments have been submitted.
