Help with formula !!

excel02021

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. 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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,991
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

How about (for "yes"):
Rich (BB code):
=countif(b1:e1,"yes")/(countif(b1:e1,"yes") + countif(b1:e1,"no"))
To check for other values (i.e. "no"), just change the value highlighted in red to the value you are looking for.
 
Solution

excel02021

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
thank you so much !! not sure, this is exactly what I was doing but opposite (I was adding and dividing later) not sure, it was BODMAS which was messing this up.
this formula works fine, so I did not understand what do you mean by changing the value in red
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,991
Office Version
  1. 365
Platform
  1. Windows
I did not understand what do you mean by changing the value in red
I just mean if you wanted to find the percentage of "no", you would just change that red "yes" to "no".
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,569
Messages
5,770,907
Members
425,651
Latest member
Mark Cashin

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
Top