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

KarenTF

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?

SteyJ

https://community.smartsheet.com/discussion/114516/if-function-with-multiple-criteria-including-blanks-how-to-structure-formula

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))))

CeliaC

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.

SteyJ

https://community.smartsheet.com/discussion/114516/if-function-with-multiple-criteria-including-blanks-how-to-structure-formula

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))))

CeliaC

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.

KarenTF

Yes that worked! Thank you! :)