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

Options
✭✭✭✭

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?

• ✭✭✭✭✭✭
Options

instead of assigning 0 to less than 70 -- can you just use two quotations?

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

Jacob Stey

• ✭✭
Options

Hi Karen,

I think you just need to add another level of IF() at the beginning to let the point be blank when the [Metric %] is blank. Hope it helps.

• ✭✭✭✭✭✭
Options

instead of assigning 0 to less than 70 -- can you just use two quotations?

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

Jacob Stey

• ✭✭
Options

Hi Karen,

I think you just need to add another level of IF() at the beginning to let the point be blank when the [Metric %] is blank. Hope it helps.

• ✭✭✭✭
Options

Yes that worked! Thank you! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!