Conditional formula IF number show "text"

Raul Cabrera

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

Kelly Moore

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

Kelly Moore

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

tanukrishna

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.

Raul Cabrera

@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

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