RE: Changing "yes" to percent

Vinton Douglas

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?

Genevieve P.

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

Genevieve P.

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

Vinton Douglas

Thanks much. Works great.