Conditional formula IF number show "text"

Raul Cabrera
Raul Cabrera ✭✭✭
edited 08/09/22 in Formulas and Functions

I need to create a formula in a new column that automatically shows " probability" depends on the % entered manually in column "Probability to win". For example if i input 5% should should "Very low"

  • Depends on % that is entered on column “Probability to win” return on “probability” column:

 If Blank – TBD

 If 0%-5% = Very Low

 If 5%-25% – Low

 If 30%-60% – Medium

 If 65%-95% – High

 If 100% - Won

The increments on Probability to win will be just on multiples of 5%

Thanks in advance as usual for your help

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Raul Cabrera

    This formula capitalizes on the fact that any IF statement will march through the formula from left to right, until the first 'True' statement occurs. Once a true is found, the formula stops. This allows the formula to be written from low value to high value, knowing the formula will keep advancing until a true statement is found.

    With this in mind - you have gaps in your data. What is supposed to happen between values of, for example, 25% and 30%. As you have written it is a gap. I ignored it (and the 60 to 65 gap, and 95 to 100 gap) and instead said anything greater than 25 but less than 60, for example, was "Medium". Please advise if this approach wasn't what you wanted. I can put your gaps into the formula.

    =IF([Probability to win]@row="","TBD", IF([Probability to win]@row<=0.05, "Very Low", IF([Probability to win]@row<=0.25, "Low", IF([Probability to win]@row<=0.6, "Medium", IF([Probability to win]@row<=95, "High", IF([Probability to win]@row<=1, "Won"))))))

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Raul Cabrera

    This formula capitalizes on the fact that any IF statement will march through the formula from left to right, until the first 'True' statement occurs. Once a true is found, the formula stops. This allows the formula to be written from low value to high value, knowing the formula will keep advancing until a true statement is found.

    With this in mind - you have gaps in your data. What is supposed to happen between values of, for example, 25% and 30%. As you have written it is a gap. I ignored it (and the 60 to 65 gap, and 95 to 100 gap) and instead said anything greater than 25 but less than 60, for example, was "Medium". Please advise if this approach wasn't what you wanted. I can put your gaps into the formula.

    =IF([Probability to win]@row="","TBD", IF([Probability to win]@row<=0.05, "Very Low", IF([Probability to win]@row<=0.25, "Low", IF([Probability to win]@row<=0.6, "Medium", IF([Probability to win]@row<=95, "High", IF([Probability to win]@row<=1, "Won"))))))

    Kelly

  • Type what you want to display if the condition is met (if it is text, then write the text within quotation marks). Type a comma. Type what you want to display if the condition isn't met. Close the bracket and hit the [Enter] key.

  • @Kelly Moore Thanks for the solution this is exactly what i was looking for. The gaps between range of numbers is because we are working just with multiple of 5, then it is not necessary to include for example numbers like 26, 33, 78, etc. Just to add more to the sheet i include another column that has a formula =MOD(([Probability to win]@row) * 100, 5). This should returns "0" remainder after a division operation, if not is because the number input in "Probability to win is not multiple of "5" and then adding conditional formatting turn red the cell.

    Again, thanks for your help.

  • Ozu
    Ozu ✭✭✭

    Hello,

    How to use this formula in smart sheets, it's extracting data in excel but in smartsheet it shows no value please help.

    =SUMIF($A$21:$A$39,"*uk*",C$21:C$39)/C$45

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!