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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks much. Works great.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!