IF Function with multiple criteria including blanks - how to structure formula?


I have working formula that will assign a point value based on a percentage value entered for a metric. The range is 100%(10pts), 90-99%(7pts), 80-89%(5pts), and 70-79% (3pts). The working formula below:

=IF([Metric %]@row <= 79, 3, IF([Metric %]@row <= 99, 7, IF([Metric %]@row = 100, 10)))

I need to be able to cap the lower range. My initial solution was to add in another IF function to account for values less than 70% and that would just assign "0" as the point-value. This worked, however, now if there is a blank value for the metric (which is possible for sites that do not have this metric applicable to them), it's still assigning the "0" point-value, but I just need for it to stay blank. How can this be achieved?

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!