Tip 076

Percentage depending on the number of filled-in cells:
Last week, I was asked by Albert Thuy, how to calculate the percentage, depending on the number of tests that are carried out.

Here’s what I came up with.
I have a table with a row of several boxes (row1).
I have a row with the maximum possible points (row 2).
And, I have a row with the points obtained (row 3).
The aim is to calculate the percentage of the number of points but, only when there are wins, including zero points (1). But when there is no participation in the test, you have no points (2) and these don’t need to be included in the percentage.

We first start with the calculation of the “to get” points.
These are the points which we get after taking a test.

For this, we use the SUMIF function. It must add the content of the cells which are non-empty. If the cell is empty, then this need not be taken into account.

We begin our formula with the equal sign (=), we type the SUMIF function and the open the parenthesis.
As you see in the image below, this function consists of three options.
“Range”, “Criteria” and “Sum_Range”.

For “Range”, we select the cells whose sum it must be calculated, i.e, the sum of points earned. In this case, it is, B3: F3.

For the second option, we determine the conditions that the contents of the cell must satisfy in order to be included in the sum.
Thus, it is greater than or equal to zero. We place this criteria in quotation marks.
“> = 0”
When a person gets a zero, it has be added to his total, to get the correct percentage.
Make sense?

The final option is the summation range.
There are many possibilities for the maximum scores in a test.
Select the range B2: F2
Close the parentheses and click Enter.

This gives us the maximum possible points.
Here it is only 400, for the simple reason that I have not taken the “Mathematics” test.

To calculate the percentage, we must first know how many points we’ve scored.
So this is the sum of B3: F3

When I use the sum of the number of points scored by the proportion of achievable points, I’ll know my percentage.

So I divide the contents of cell C6 (points scored) by cell C5 (score).
Note that the format of the cell in question has to be set to “Percentage”.

Now we merge these two formulas.
How do we do this?
We need the sum of points obtained (C6) divided by the maximum possible points that can be obtained which, we had calculated with the SUMIF (B3: F3, “> = 0”, B2: F2) function.

So as always, we start our formula with the equal sign (=) and then type:
C6 / (SUMIF (B3: F3, “> = 0”, B2: F2))

In order to maintain order, I have placed the total points scored in cell G3.
If you also do this, do not forget to adjust the formula.
So, G3 is divided by the SUMIF function.

If you change the maximum number of points that can be obtained, or a cell with the obtained points, then the extracted percentage will always reflect this change.

Awesome!
You've completed Tip 076 START NEXT LESSON

Office 2007 - Excel Tips 2007 - 2013

Tip 076

Featured Courses