excel02021
New Member
- Joined
- Nov 16, 2021
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
I need help with the formula: I tried everything including Countif - countif and counta, But this is what I want.
I have 5 columns = a,b,c,d,e
each column has yes, no and n/a as value (column d to e)
in columng a I need to find % of yes value.
Easy part is if column b, c, d, e has just yes or no as value.
So for example: B=yes, c=yes, d=yes,e=yes than a = 100%
another example: B=yes, c=yes, d=yes, e=no than a = 75%
so it is easy to write formula if it just yes or no, but there is another value n/a.
If the value is n/a than that column should not count.
so if B=yes, c=yes,d=yes,e=no than formula is countif(b1:e1,"yes")/counta(b1:e1,"no") which is 75% 0r it divides by 4 column.
But if the value is B=yes, c=yes, d=yes, e=n/a than it should divide by 4 column it only should divide by 4 column but only 3 column because any column with value n/a does not count.
so in this case if B=yes, c=yes, d=yes, e=n/a , value of a (answer) should be still 100% because column with n/a does not count and rest of them are yes.
but instead of n/a if it is no, than value will be 75%.
last one more example if B=yes, c=no, d=n/a, e=n/a than value of a = 50% because it only divides by 2 column (other two column with n/a value does not count.
Hope I was able to explain the problem.
after every try i cannot come with one formula which can resolve that issue.
I have 5 columns = a,b,c,d,e
each column has yes, no and n/a as value (column d to e)
in columng a I need to find % of yes value.
Easy part is if column b, c, d, e has just yes or no as value.
So for example: B=yes, c=yes, d=yes,e=yes than a = 100%
another example: B=yes, c=yes, d=yes, e=no than a = 75%
so it is easy to write formula if it just yes or no, but there is another value n/a.
If the value is n/a than that column should not count.
so if B=yes, c=yes,d=yes,e=no than formula is countif(b1:e1,"yes")/counta(b1:e1,"no") which is 75% 0r it divides by 4 column.
But if the value is B=yes, c=yes, d=yes, e=n/a than it should divide by 4 column it only should divide by 4 column but only 3 column because any column with value n/a does not count.
so in this case if B=yes, c=yes, d=yes, e=n/a , value of a (answer) should be still 100% because column with n/a does not count and rest of them are yes.
but instead of n/a if it is no, than value will be 75%.
last one more example if B=yes, c=no, d=n/a, e=n/a than value of a = 50% because it only divides by 2 column (other two column with n/a value does not count.
Hope I was able to explain the problem.
after every try i cannot come with one formula which can resolve that issue.