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), 9099%(7pts), 8089%(5pts), and 7079% (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 pointvalue. 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" pointvalue, but I just need for it to stay blank. How can this be achieved?
Best Answers

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

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.
Answers

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

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.

Yes that worked! Thank you! :)
Help Article Resources
Categories
Check out the Formula Handbook template!