Previous Topic Next Topic

Excel Help with Percent calculations

Solved/Closed
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
4479
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 replies

Answer 1 / 3
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:

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

Let me know if it helps or solves your problem:
OT
12
skopo
Apr 23, 2009 at 10:11 AM
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
0
EXCELAMATEUR
Sep 25, 2009 at 01:04 AM
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.
0
Mickey
Mar 25, 2010 at 05:03 AM
it was dirty!
0
FIROZ
May 22, 2010 at 01:14 AM
The principle amount of 50000. profit is 4000/10days . what is the percentage
0
Answer 2 / 3
deepi
Apr 22, 2009 at 03:00 AM
what is the difference between percentage and average with suitable examples
8
Answer 3 / 3
Gcina
Jun 13, 2010 at 11:41 AM
How to return the average of arguments in the cell rather than the formula; formula to appear in the formula field only,
4
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Jun 13, 2010 at 02:13 PM
what are you looking for again. Could you explain more.
0