IF AND statement with multiple criteria getting error messages
I have created a semi circle donut dashboard using the following table and formulas. to date this has worked because the raw data has been manually entered and our teams are using a forcing function of entering 0.25, 0.5, 0.75 or 1. The semi circle is dynamic and acts like a speedometer showing a particle color and point in the curve dependent on the number.
In order to make this work if one cell in a row has a number the others show zero (using a 0.0001 placeholder) which keeps the dashboard colors fixed
=IF(Utilization = 0.25, Utilization, 0.0001)
=IF(Utilization = 0.5, Utilization, 0.0001)
=IF(Utilization = 0.75, Utilization, 0.00001)
=IF(Utilization = 1, Utilization, 0.0001)
I need to move to a more dynamic formula that allows each formula to look for a range of numbers and if its not within that range it should display zero (0.00001). The row can not have two cells with numbers in it
I have tried a number of different iterations using I/AND, IF/OR, CONTAINS, and can not find a solution that works. Conceptually below is what I want to get to
=IF(AND(Utilization > 0.5, Utilization < 0.75, Utilization, 0.00001))
with the following ranges
0 to 0.49 RED
0.5 to 0.74 YELLOW
0.75 to 0.80 GREEN
0.81 to 1 RED
Best Answer

Thank you! It looks like the issue was I mistakenly kept letting it refer to a specific cell vs using @row
Answers

@Tarek A I would love to help but I'm having trouble following based on what you've provided.
=IF(Utilization = 0.25, Utilization, 0.0001) is not a valid formula unless you specify a row for the Utilization field:
=IF(Utilization@row = 0.25, Utilization@row, 0.0001) which means if the Utilization column on this row equals 0.25, place that value here, otherwise, set the value to 0.0001.
but I don't see how that forces other columns in a given row to be blank if there's a value in the Utilization column.
Now considering your stated criteria below this, what fields are you expecting these to go into from your screenshot, and what are you doing with the colors? This doesn't make any real sense.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hi Jeff 
correct, the formula is Utilization@row. The column header is named differently and I didn't want to confuse the discussion.
Each cell in a row is currently using the following formulas:
=IF(Utilization@row = 0.25, Utilization@row, 0.0001)
=IF(Utilization@row= 0.5, Utilization@row, 0.0001)
=IF(Utilization@row= 0.75, Utilization@row, 0.00001)
=IF(Utilization@row= 1, Utilization@row, 0.0001)
The way the table works, depending on the utilization value it will fill in one particular cell (under utilized, target utilization or over utilized) For example a 0.25 utilization will only populate one cell, the other cells will populate 0.00001 and I am using the decimal decrease icon to make it look like a Zero. Thus creating the 'speedometer' like dashboard
I need to have formulas that look at a range of numbers and only populate one cell with the utilization while the others populate 0.00001. Currently I can not figure out how to use IF(AND) with <= , >=
For example I would expect
0 to 0.49 RED to translate to
=IF(AND(Utilization@row >=0, Utilization@row =< 0.49), Utilization@row, 0.0001))
0.5 to 0.74 YELLOW
0.75 to 0.80 GREEN
0.81 to 1 RED

=IF(AND(Utilization@row >= 0, Utilization@row <= 0.49), Utilization@row, 0.0001)
The above will work. Make sure your operators are always "greater than or equal to" (>=) or "Less than or equal to" (<=).
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you! It looks like the issue was I mistakenly kept letting it refer to a specific cell vs using @row
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!