IF AND statement with multiple criteria getting error messages

Tarek A

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

Tarek A

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

Jeff Reisman

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

Tarek A

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

Jeff Reisman

=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" (<=).

Tarek A

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