If and formula help
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", )
Answers
-
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. -
thanks Chris, your first one has worked. TVM for your help
-
Awesome!
Happy this worked for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 395 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!