Percentage Formula

avistasys

New Member
Joined
May 4, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I am unable to work out or find a solution online for the following sheet.

I have a number of tasks to complete which are recorded as YES or No, as not all tasks are required for certain products these are recorded as NA. (drop downs)

The problem I face is to adjust the overall percentage of the sheet when NA is recorded, currently these affect the percent compliance if used.

What changes do I need to make for the formula please. I have attached an image.

Many thanks in advance.

=COUNTIF(E2:E13,"Yes")
=COUNTIF(E2:E13,"No")
=COUNTIF(E2:E13,"NA")

=SUM(E14/(B13-E16))

Kind regards
Avistasys
 

Attachments

  • Excel Query.jpg
    Excel Query.jpg
    62.2 KB · Views: 7
The images I sent shows the anomaly where the NA affects the percentage
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I am not saying this to be mean, but I question if you understand the mathematical concept of percentages and how they work.
Or if this is really not a mathematical percentage question at all, but something totally different that you are having a hard time explaining.

Compliance Percentage

10 Yes = 100%
9 Yes = 90%
8 Yes = 80%.... and so on

1 No = -100%
2 No = - 905
3 No = - 80%... and so on
That only works that way if you have EXACTLY a total of 10 things to count. But if you have 10 YES and 1 NO, the total is 11.

Another way to look at it, is you started with 12, and there is 1 NA, so taking one away leaves 11.

So, to get the percentage, you would take the total number of "Yes" responses divided by the total number of non-NA responses, which would be:
=10/11 = 91%

Anything else is not truly the percentage of "Yes" to "non-NA" answers (which you claim is what you are after).
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top