Conditional formula IF number show "text"
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
Best 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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!