Excel - Calculation on excel sheet

December 2016




Issue


I used "=AVERAGE(B2:AR2)" to calculate percentages based on information from 40 sheets. Example, On one sheet that compiles the data from 40 sheets will have cells with 100%, 0%, 80%, 0% etc..(Usually the 0% indicates no activity in the cells and Excel does not count the zeros). The Average formula seems to work like I want it. However, there are occasions where I do want Excel to include zero percent. When I manually type the zero in the cell on one of my sheets, my chart reflects the correct calculation but of course I delete the formula.

Solution


At first I didnt understand, but then I did some of my own example and see what you mean. I used three formulas with differeing results when the 0% is deleted and left with a blank.
I started with AVERAGE(B2:AR2) and tested it against SUM(B2:AR2)/COUNTA(B2:AR2): which is just the average formula in its mathimatical simplest. sum of the numbers divided by sum of the number of data points. I thought that the counta would count the blank, but as i should have known it only counts if there is something in the cell. So this however gave me the same result when the 0%s were omitted.

I then did the same thing, but to count the data points, as I assume you have data in each cell from B2-AR2, I used a more complicated way to count. I did:

SUM(B2:AR2)/(COLUMN(AR2)-COLUMN(B2)+1)

This worked when 0%s were omitted since it wasn't counting anything but the column reference numbers which are absolute. the +1 is included in the end because the number is inclusive.

(obviously format the cell as a Percent to get the %)

Related :

This document entitled « Excel - Calculation on excel sheet » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.