RE: Changing "yes" to percent
Hello,
I have some questions which the answers can either be :
Yes & No=50%
No & Yes=50%
Yes & Yes=100%
No & No=0%
Yes or No=100% or 50%
What formulla can I use to create such outcome, i.e, converting 'yes' and 'no' to percent?
Best Answer

The formula for this will depend on your sheet setup. I presume that there are two different columns that record the two answers and that they are next to each other, is that correct?
If so, we can use COUNTIF to count how many "Yes" answers there are. If there are 2, it's 100%. If there is 1, 50%. If there are no "Yes" answers, then it means both must be "No", so it's 0%.
Percents are decimals in formulas, so 1 = 100%, etc. You'll want to set the column with the formula to a % format.
Try this:
=IF(COUNTIF([Column 1]:[Column 2], "Yes") = 2, 1, IF(COUNTIF([Column 1]:[Column 2], "Yes") = 1, 0.5, 0))
The only instruction that may be tricky is your last statement, where you say: Yes or No=100% or 50%
If the formula above isn't correct, would you be able to provide a screen capture of your sheet setup (but block out sensitive data), and explain how the last statement works?
Thanks!
Genevieve
Answers

The formula for this will depend on your sheet setup. I presume that there are two different columns that record the two answers and that they are next to each other, is that correct?
If so, we can use COUNTIF to count how many "Yes" answers there are. If there are 2, it's 100%. If there is 1, 50%. If there are no "Yes" answers, then it means both must be "No", so it's 0%.
Percents are decimals in formulas, so 1 = 100%, etc. You'll want to set the column with the formula to a % format.
Try this:
=IF(COUNTIF([Column 1]:[Column 2], "Yes") = 2, 1, IF(COUNTIF([Column 1]:[Column 2], "Yes") = 1, 0.5, 0))
The only instruction that may be tricky is your last statement, where you say: Yes or No=100% or 50%
If the formula above isn't correct, would you be able to provide a screen capture of your sheet setup (but block out sensitive data), and explain how the last statement works?
Thanks!
Genevieve

Thanks much. Works great.
Help Article Resources
Categories
Check out the Formula Handbook template!