Averaging Noncontiguous Cells That Contain Some Zero Values

BY STANLEY ZAROWIN

Q: Here’s my problem: I need to calculate the average for a bunch of cells that not only are not contiguous but some contain zero values, and I don’t want them to affect the results. Do you have any ideas?

 

A: It is a little tricky. The solution, as you’ll see, lies in the denominator, where we need to eliminate the effect of the zero values. Once we do that, the rest falls into place. For example, if the target cells are A1, C1, E1 and G1, the averaging formula would be:

 

=(A1+C1+E1+G1)/((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0))

 

By canceling all the cells that produce zero values, we obtain the correct average.

 

More from the JofA:

 

 Find us on Facebook      Follow us on Twitter

 

SPONSORED REPORT

Cybersecurity threats proliferating for midsize and smaller businesses

This report details how SMBs can properly protect private information from breaches, design and implement a cybersecurity policy, and create safeguards for training and education.

QUIZ

News quiz: Senate health care bill in the spotlight

Reports related to the Republican bill to repeal many provisions of the PPACA, other tax issues, and the giant AICPA ENGAGE Conference offered a diverse reading list for June. See how much you know about recent news with this short quiz.