Excel Help with Percent calculations

Scorchy Posts 2 Registration date Wednesday February 4, 2009 Status Member Last seen February 5, 2009 - Feb 5, 2009 at 01:25 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 13, 2010 at 02:13 PM
Hello, I use "=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.

Help Please if you can understand my problem.

3 responses

Oterwill Posts 5 Registration date Tuesday February 24, 2009 Status Member Last seen February 25, 2009 19
Feb 25, 2009 at 07:43 AM
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:


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 %)

Let me know if it helps or solves your problem:
could you please help me
with the following for example let me say cell a3+d3+d4 maube I get the answe as 40 then I want that 40 on the other cell maybe f3 to be 2 as a level in other wors from 40 to 49 must be level 2 automatically , I need a formular to do it

please help
I liked your formula; however, do you know how to do that same formula, but assuming all the points do not contain data. I only want it to count the # of points that contain data then divide the total # of points containing data by 56.

Example: I have a total of 56 cars, I can drive daily. (Next to each car listed separately, car 1, car 2, car 3, etc, I list the # of passengers in that car if it were used that day). I want the formula to count the # of cars used that day (based on the cells that have passengers in them) and divide that total by 56.
it was dirty!
The principle amount of 50000. profit is 4000/10days . what is the percentage
what is the difference between percentage and average with suitable examples
How to return the average of arguments in the cell rather than the formula; formula to appear in the formula field only,
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 13, 2010 at 02:13 PM
what are you looking for again. Could you explain more.