new to Smartsheet and struggling with If / And Formula

Fialko66 · 02/02/22

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",""))

Paul Newcome · 02/03/22

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?

Fialko66 · 02/02/22

Amended formula

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

Paul Newcome · 02/02/22

Are you able to provide screenshots for reference?

Fialko66 · 02/02/22

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

Jeff Reisman · 02/02/22

https://community.smartsheet.com/discussion/comment/318280#Comment_318280

Rather than a super-long 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.

Paul Newcome · 02/02/22

Why are you outputting "TWO" instead of just the number 2?

Fialko66 · 02/03/22

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

Paul Newcome · 02/03/22

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?

new to Smartsheet and struggling with If / And Formula

Best Answer

Answers

Help Article Resources

Categories