Percentage Formula

[avistasys]

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

 

[Joe4]

Like Fluff, I am confused as to what you are looking for.
What is your expected result for the first example you gave?
Can you provide other examples, and let us know what your expected result is?
That may help clarify thing.

 

[avistasys]

Sorry Fluff,

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

Sorry all for your assistance.

I shall try to elaborate...

Lets say there are five tasks that require undertaking for full compliance. Completing all tasks (YES) would be 100%
Any NO's would reduce the percentage.

The anomaly is when NA is used...
If for instance Task 2 is not required to be undertaken on a certain product, NA should be recorded, BUT.... It should change the number of overall tasks to 4 to adjust the compliance percentage for four tasks not five.

1 = Sanding
2 = Washing
3 = Painting
4 = Blowing
5 = Sealing

If all five tasks are accomplished, YES is recorded for each. Therefore 100% compliant

If some tasks are not accomplished, NO is recorded.

If one task is not required NA should be recorded and the percentage lowered accordingly.

Hope this helps clarify what I'm trying to achieve.

Kind regards

 

[Fluff]

I'm sorry but that is even more confusing. With the data I posted in post#10 what would your expected results be?

 

[Joe4]

Can you please just show us examples with expected results?
As they say, a picture often says a thousand words.

 

[avistasys]

Hopefully this might make things clearer. The thing to bare in mind, an NA

I have attached 3 images

Image 1 shows all tasks been accomplished. All YES and 100% Compliance

Image 2 shows task 2 not been accomplished. Nine YES and one NO, 92% Compliance

Image 3 shows task 2 not been accomplished and task 6 not undertaken. Nine YES, one NO and one NA, 91% Compliance... It should be 92% as Nine tasks are undertaken, not Eight!

 

[Fluff]

If you mean that a NA should count as a Yes, then use
=(E14+E16)/12

 

[Joe4]

Image 3 shows task 2 not been accomplished and task 6 not undertaken. Nine YES, one NO and one NA, 91% Compliance... It should be 92% as Nine tasks are undertaken, not Eight!

Where are you getting 9 and 8 from???
You have 10 Yes, 1 No, and 1 NA.

 

[avistasys]

No NA should not affect the percentage.

For the purpose of clarity. Take the example of 10 tasks

Each YES carries + 10%
Each NO carries - 10%
Each NA must not affect the percentage in anyway.

 

[Joe4]

Each NA must not affect the percentage in anyway.

That is exactly what Fluff and I have already given you!

Stick with the last example you posted above, where you have:
10 Yes
1 No
1 NA

Please tell us exactly what you expect to return as your percentage, and explain exactly how you arrive at that number.
Please do not give us a general or high level explanation. We want to say an explanation of this particular example, so be sure to include the numbers from this example in your explanation.

 

[avistasys]

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

NA - No affect