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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Vinton Douglas

    The formula for this will depend on your sheet set-up. 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 set-up (but block out sensitive data), and explain how the last statement works?

    Thanks!

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Vinton Douglas

    The formula for this will depend on your sheet set-up. 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 set-up (but block out sensitive data), and explain how the last statement works?

    Thanks!

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Thanks much. Works great.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!