» 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:
- Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Essentials of Accounting and Post Test Booklet 8, Eighth Edition
- Marketing Plan: A Handbook with Marketing Plan
- Keys to Reading an Annual Report (Barron's Business Keys)
No comments have been submitted.
