When you have a worksheet containing a mixture of tasks completed and those not done, you may want to calculate the project complete percentage. There is an easy formula in Excel that uses the COUNTA function to achieve this. This is demonstrated below:
Calculate a project complete percentage in Excel
- To calculate project complete percentage in Excel using the COUNTA function, follow the procedure below:
- Click on Formulas on the menu bar in Excel.
- Next, click on More Functions and point the cursor on
- Scroll down the list that displays and click on
The dialogue box will display as shown below.
Before now, you should have typed the tasks and marked “done” against those that have been completed, and left blank those that haven’t been completed.
Figure 1: How to access the PROPER formula in Excel
- In the Value 1 field, enclose in brackets the range of cells in the column with some tasks marked done. In the example below, (E4:E7).
- Next, without leaving any space, draw a forward slash and type COUNTA and again enclose the total range of cells containing both the tasks done and those not done on the left. In the example below, (B4:B7).
- Thereafter, click on
It will show you the result displayed in the screenshot below:
Figure 2: Project complete percentage by COUNTA
Interpretation of the Result
In the example displayed in Figure 2 above, the items in the column on the left are the total number of tasks the person has identified that need to be done, while those marked “done” on the right are the tasks that have been done. Using the COUNTA function in Excel as described above, the project complete percentage has been calculated.
This has been achieved by using the formula
=COUNTA(E4:E7)/COUNTA(B4:B7) in the example above. You can easily look at the table and verify through a manual calculation that the project complete percentage is actually 75%.
However, if the result displayed is in currency or decimal form, do the following:
- Go to Home on the menu bar.
- Make sure Percentage is chosen in the box on the far right in the screenshot below.
Figure 3: Project complete percentage
- If anything else is chosen, click on it and select Percentage from the list of items displayed on the drop-down menu.
Another way to calculate the project complete percentage is to simply type the formula above into the cell in which you want the project complete percentage to display. Hit the enter key when you finish typing, and the result will show.
How the formula functions
What this formula does essentially is to just divide the tasks marked done by the total number of tasks. The result is then converted to the percentage and presented. In the example above, the complete tasks are counted by counting the cells on the right column that are not blank from E4 to E7.
). The total number of tasks is counted on the left using the same function as:
). The first count is then divided by the second count and the result presented in the percentage format.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.