Download Salary Calculation Sheet and Salary Slip in Excel December 21, 2015 by Raju Choudhary 51 Comments Download Salary Calculation Sheet and Salary Slip in.

• • 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).

• • 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).

Grupo Bryndis Grandes Exitos Rar. 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.