If and formula help

Archie

Trying to create the below formula but its coming up with unparseable…

=IF(AND(Performance@row <2, Trust@row <2), "C1", =IF(AND(Performance@row <2, Trust@row = >2 <3), "C2", =IF(AND(Performance@row <2, Trust@row = >3 <4), "C3", =IF(AND(Performance@row <2, Trust@row = >4 <5), "B10", =IF(AND(Performance@row <2, Trust@row = >5), "B11", =IF(AND(Performance@row =>2 <3, Trust@row <2), "C4", =IF(AND(Performance@row = >2 <3, Trust@row = >2 <3), "C5", =IF(AND(Performance@row = >2 <3, Trust@row = >3 <4), "C6", =IF(AND(Performance@row = >2 <3, Trust@row = >4 <5), "B8", =IF(AND(Performance@row = >2 <3, Trust@row = >5), "B9", =IF(AND(Performance@row = >3 <4, Trust@row <2), "C7", =IF(AND(Performance@row = >3 <4, Trust@row = >2 <3), "C8", =IF(AND(Performance@row = >3 <4, Trust@row = >3 <4), "C9", =IF(AND(Performance@row = >3 <4, Trust@row = >4 <5), "B6", =IF(AND(Performance@row = >3 <4, Trust@row = >5), "B7", =IF(AND(Performance@row = >4 <5, Trust@row <2), "C10", =IF(AND(Performance@row = >4 <5, Trust@row = >2 <3), "C11", =IF(AND(Performance@row = >4 <5, Trust@row = >3 <4), "B5", =IF(AND(Performance@row = >4 <5, Trust@row = >4 <5), "A1", =IF(AND(Performance@row = >4 <5, Trust@row = >5), "A2", =IF(AND(Performance@row = >5 <5.5, Trust@row <2), "C12", =IF(AND(Performance@row = >5 <5.5, Trust@row = >2 <3), "B3", =IF(AND(Performance@row = >5 <5.5, Trust@row = >3 <4), "B4", =IF(AND(Performance@row = >5 <5.5, Trust@row = >4 <5), "A3", =IF(AND(Performance@row = >5 <5.5, Trust@row = >5), "A4", =IF(AND(Performance@row = >5.5, Trust@row <2), "C13", =IF(AND(Performance@row = >5.5, Trust@row = >2 <3), "B1", =IF(AND(Performance@row = >5.5, Trust@row = >3 <4), "B2", =IF(AND(Performance@row = >5.5, Trust@row = >4 <5), "A5", =IF(AND(Performance@row = >5.5, Trust@row = >5), "A6", )

=Chris Palmer

Hello @Archie,

By looking at the surface replace "=IF("
with "IF(" in all instances except the first one.
In addition, for every "IF(" you need an end parentheses ")" to close your statement.

Like so:

=IF(AND(Performance@row < 2, Trust@row < 2), "C1",
IF(AND(Performance@row < 2, Trust@row >= 2, Trust@row < 3), "C2",
IF(AND(Performance@row < 2, Trust@row >= 3, Trust@row < 4), "C3",
IF(AND(Performance@row < 2, Trust@row >= 4, Trust@row < 5), "B10",
IF(AND(Performance@row < 2, Trust@row >= 5), "B11",
IF(AND(Performance@row >= 2, Performance@row < 3, Trust@row < 2), "C4",
IF(AND(Performance@row >= 2, Performance@row < 3, Trust@row >= 2, Trust@row < 3), "C5",
IF(AND(Performance@row >= 2, Performance@row < 3, Trust@row >= 3, Trust@row < 4), "C6",
IF(AND(Performance@row >= 2, Performance@row < 3, Trust@row >= 4, Trust@row < 5), "B8",
IF(AND(Performance@row >= 2, Performance@row < 3, Trust@row >= 5), "B9",
IF(AND(Performance@row >= 3, Performance@row < 4, Trust@row < 2), "C7",
IF(AND(Performance@row >= 3, Performance@row < 4, Trust@row >= 2, Trust@row < 3), "C8",
IF(AND(Performance@row >= 3, Performance@row < 4, Trust@row >= 3, Trust@row < 4), "C9",
IF(AND(Performance@row >= 3, Performance@row < 4, Trust@row >= 4, Trust@row < 5), "B6",
IF(AND(Performance@row >= 3, Performance@row < 4, Trust@row >= 5), "B7",
IF(AND(Performance@row >= 4, Performance@row < 5, Trust@row < 2), "C10",
IF(AND(Performance@row >= 4, Performance@row < 5, Trust@row >= 2, Trust@row < 3), "C11",
IF(AND(Performance@row >= 4, Performance@row < 5, Trust@row >= 3, Trust@row < 4), "B5",
IF(AND(Performance@row >= 4, Performance@row < 5, Trust@row >= 4, Trust@row < 5), "A1",
IF(AND(Performance@row >= 4, Performance@row < 5, Trust@row >= 5), "A2",
IF(AND(Performance@row >= 5, Performance@row < 5.5, Trust@row < 2), "C12",
IF(AND(Performance@row >= 5, Performance@row < 5.5, Trust@row >= 2, Trust@row < 3), "B3",
IF(AND(Performance@row >= 5, Performance@row < 5.5, Trust@row >= 3, Trust@row < 4), "B4",
IF(AND(Performance@row >= 5, Performance@row < 5.5, Trust@row >= 4, Trust@row < 5), "A3",
IF(AND(Performance@row >= 5, Performance@row < 5.5, Trust@row >= 5), "A4",
IF(AND(Performance@row >= 5.5, Trust@row < 2), "C13",
IF(AND(Performance@row >= 5.5, Trust@row >= 2, Trust@row < 3), "B1",
IF(AND(Performance@row >= 5.5, Trust@row >= 3, Trust@row < 4), "B2",
IF(AND(Performance@row >= 5.5, Trust@row >= 4, Trust@row < 5), "A5",
IF(AND(Performance@row >= 5.5, Trust@row >= 5), "A6", "")))))))))))))))))))))))))))

This is another option with less complexity you can experiment with.

	=IF(AND(Performance@row < 2, Trust@row < 2), "C1", IF(AND(Performance@row < 2, Trust@row < 3), "C2", IF(AND(Performance@row < 2, Trust@row < 4), "C3", IF(AND(Performance@row < 2, Trust@row < 5), "B10", IF(AND(Performance@row < 2), "B11", IF(AND(Performance@row < 3, Trust@row < 2), "C4", IF(AND(Performance@row < 3, Trust@row < 3), "C5", IF(AND(Performance@row < 3, Trust@row < 4), "C6", IF(AND(Performance@row < 3, Trust@row < 5), "B8", IF(AND(Performance@row < 3), "B9", IF(AND(Performance@row < 4, Trust@row < 2), "C7", IF(AND(Performance@row < 4, Trust@row < 3), "C8", IF(AND(Performance@row < 4, Trust@row < 4), "C9", IF(AND(Performance@row < 4, Trust@row < 5), "B6", IF(AND(Performance@row < 4), "B7", IF(AND(Performance@row < 5, Trust@row < 2), "C10", IF(AND(Performance@row < 5, Trust@row < 3), "C11", IF(AND(Performance@row < 5, Trust@row < 4), "B5", IF(AND(Performance@row < 5, Trust@row < 5), "A1", IF(AND(Performance@row < 5), "A2", IF(AND(Performance@row < 5.5, Trust@row < 2), "C12", IF(AND(Performance@row < 5.5, Trust@row < 3), "B3", IF(AND(Performance@row < 5.5, Trust@row < 4), "B4", IF(AND(Performance@row < 5.5, Trust@row < 5), "A3", IF(AND(Performance@row < 5.5), "A4", IF(AND(Performance@row >= 5.5, Trust@row < 2), "C13", IF(AND(Performance@row >= 5.5, Trust@row < 3), "B1", IF(AND(Performance@row >= 5.5, Trust@row < 4), "B2", IF(AND(Performance@row >= 5.5, Trust@row < 5), "A5", IF(AND(Performance@row >= 5.5), "A6", "")))))))))))))))))))))))))

It is possible another error will occur,
but let me know the result so we can explore what that is.

A screen shot of the formula bar would be super helpful :-)

You may benefit from having a separate sheet with two columns as a matrix or legend.

For example:

Column one being the range of numbers 1.0 through 5.5
Column two being the result for those numbers A1 through C13

Then, use an INDEX MATCH formula in your primary sheet.
Reference the RANGE of column two in the matrix/legend sheet with the results.

Archie

thanks Chris, your first one has worked. TVM for your help

=Chris Palmer

https://community.smartsheet.com/discussion/comment/455388#Comment_455388

Awesome!
Happy this worked for you.