Search : in
By :

Excel Help with Percent calculations

Last answer on Sep 25, 2009 6:04:49 am BST Scorchy, on Feb 5, 2009 6:25:32 pm GMT 
 Report this message to moderators

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.

Configuration: Windows XP
Google Chrome

Best answers for « Excel Help with Percent calculations » in :
Memorize constants in Excel ShowMemorize constants in Excel You have created your table, and there are several constants used several times (VAT ,per hour, etc. ..). Solution 1[Basic]: you write them in your forms, ex: = C3 * 1196 to calculate a TTC value from an...
How to convert Excel into PDF? ShowHow to convert Excel into PDF? Here is a small tips about how to convert your excel files into PDF for your presentation. Step 1 PDF995 is software that gets installed on your computer which enables you to print any sources of document to...
Import contacts from Excel to Outlook ShowImport contacts from Excel to Outlook Issue Solution Issue How to import a contact list from Excel to Outlook and having them in a single list of contact , and not as splited items? Solution By using the software Excel 2000 and...
Download The Love Calculator ShowLove is a big word, then all means are good to find it. The name of each can say about it on compatibility between two persons a lot. The Love Calculator is a program which counting affinity between two individuals. It is not a precise science,...
Download All-In-One Calculator ShowAll-In-One Calculator is a scientific calculator with many features. It allows you to modify the data when calculating (add, delete or rectify values). It includes the following functions: a currency conversion, percentage calculation, formula...
Download PERCENT Show
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

Oterwill, on Feb 25, 2009 12:43:56 pm GMT
  • +4

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

   
Reply to Oterwill

3

skopo, on Apr 23, 2009 3:11:22 pm BST
  • +2

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

   
Reply to skopo

4

 EXCELAMATEUR, on Sep 25, 2009 6:04:49 am BST

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.

   
Reply to EXCELAMATEUR

2

deepi, on Apr 22, 2009 8:00:11 am BST
  • +4

What is the difference between percentage and average with suitable examples

   
Reply to deepi
Ask your question Reply
They need your help