I'm trying to create a formula to map to a matrix

Allison H

I know that I can't create a matrix in Smartsheet, but I'm working on creating a formula using two columns and need to have it return one of 4 answers.

If Column A is less than 3 and Column B is less than 3, then the answer should be "Bottom Left Corner"

If Column A is less than 3 and Column B is greater than 3, then the answer should be "Top Left Corner"

If Column A is greater than 3 and Column B is greater than 3, then the answer should be "Top Right Corner"

If Column A is greater than 3 and Column B is less than 3, then the answer should be "Bottom Right Corner"

I'm struggling with my If and And statements and nesting them. Any advice?

Thanks!

Andrée Starå

Hi Allison,

Try something like this.

I added the ISBLANK check so it works if one of the cells is blank.

=IF(OR(ISBLANK(A@row); ISBLANK(B@row)); ""; IF(AND(A@row < 3; B@row < 3); "Bottom Left Corner"; IF(AND(A@row < 3; B@row > 3); "Top Left Corner"; IF(AND(A@row > 3; B@row > 3); "Top Right Corner"; IF(AND(A@row > 3; B@row < 3); "Bottom Right Corner")))))

The same version but with the below changes for your and others convenience.

=IF(OR(ISBLANK(A@row), ISBLANK(B@row)), "", IF(AND(A@row < 3, B@row < 3), "Bottom Left Corner", IF(AND(A@row < 3, B@row > 3), "Top Left Corner", IF(AND(A@row > 3, B@row > 3), "Top Right Corner", IF(AND(A@row > 3, B@row < 3), "Bottom Right Corner")))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Allison,

Try something like this.

I added the ISBLANK check so it works if one of the cells is blank.

=IF(OR(ISBLANK(A@row); ISBLANK(B@row)); ""; IF(AND(A@row < 3; B@row < 3); "Bottom Left Corner"; IF(AND(A@row < 3; B@row > 3); "Top Left Corner"; IF(AND(A@row > 3; B@row > 3); "Top Right Corner"; IF(AND(A@row > 3; B@row < 3); "Bottom Right Corner")))))

The same version but with the below changes for your and others convenience.

=IF(OR(ISBLANK(A@row), ISBLANK(B@row)), "", IF(AND(A@row < 3, B@row < 3), "Bottom Left Corner", IF(AND(A@row < 3, B@row > 3), "Top Left Corner", IF(AND(A@row > 3, B@row > 3), "Top Right Corner", IF(AND(A@row > 3, B@row < 3), "Bottom Right Corner")))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Allison H

Andrée,

You are a miracle worker! The formula worked. Some of the fields weren't returning, but when I changed all of my ">" to ">=" then everything worked as expected. Thank you!

Best,

Allison

Andrée Starå

Excellent!

I'm always happy to help!

✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!