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?
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?

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?

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?
Help Article Resources
Categories
Check out the Formula Handbook template!