Excel - Calculation on excel sheet

April 2017




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


Published by aakai1056. Latest update on May 3, 2012 at 08:56 AM by aakai1056.
This document, titled "Excel - Calculation on excel sheet," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).