new to Smartsheet and struggling with If / And Formula
Hi. I'm fairly new user to Smartsheet and struggling with formulas i could create in excel. I have a sheet that will generate a RAG status based on the combined scores of two columns (likelihood and impact) but cannot get my If /And formula to work at all. I am user a separate helper sheet to generate the results. The plans was to then nest all of the results but i can't get the first expression to work, never mind nesting them. Can anyone point me in the right direction please?
I have looked at examples but still don't fully follow them to be honest :(
=IF(AND({GMSIT RISK REGISTER Range 1}=1,[{GMSIT RISK REGISTER Range 2}=1."G",""))
Best Answer

You can use a matrix in Smartsheet as well. Are you able to provide a screenshot of the matrix along with the plain text version of the logic you are wanting to use?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

Amended formula
=IF(AND([CURRENT RISK SCORE (LIKELIHOOD)]@row=1,[CURRENT RISK SCORE (IMPACT)]@row=1),"TWO", "NEXT")

Are you able to provide screenshots for reference?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Hi Paul. These are the 2 fields and the score can be anything from 2 to 25 so if i can get one IF/AND expression working the plan is to do a nested IF /AND for the rest?
Thank you

Rather than a superlong series of nested IFs, how about a helper sheet or some helper columns? Make a number list from 2 to 25 in one "Number" column, and the status values that go with each number in a "Status" column. Then use an INDEX/MATCH in your Current Rag Status column.
For helper columns on the same sheet:
=INDEX(Status:Status, MATCH(SUM([CURRENT RISK SCORE (LIKELIHOOD)]@row:[CURRENT RISK SCORE (IMPACT)]@row), Number:Number, 0))
In English, find the status value in the status column where the sum of my risk score columns equals the value in the Number column.
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!

Why are you outputting "TWO" instead of just the number 2?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Hi Paul / Jeff
I'm not sure either methods mentioned above will work now after having another look as a total score of 6 could have 2 different rag ratings depending on how the total was obtained e.g. a score of 1 on the likelihood column and 5 on the impact column (6) would be different RAG rating to a score of the likelihood column and a score of 1 on the impact columns (6) so even more possible outcomes than i originally thought!
In my second formula example i was just using any text value (TWO) to see if i could get it working then could easily have changed the output afterwards.
The original excel uses a 5 x 5 matrix to generate the various RAGs
Sorry, if this sounds confusing but it is to me and i've used excel for yrs but don't understand how the person that created the original set it up

You can use a matrix in Smartsheet as well. Are you able to provide a screenshot of the matrix along with the plain text version of the logic you are wanting to use?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!