# Multiple IF Formula for Matrix

✭✭✭✭
edited 03/11/21

Hello,

I'm looking for help on an IF formula which looks at multiple columns to determine a value in a column. The image attached displays the guidelines on how to populate the "Matrix Position" column. The formula will look at the Influence + Impact columns in order to populate the "Matrix Position". I'm stuck.

This is an image of my smartsheet:

--Lisa

• ✭✭✭✭✭✭

Hi Lisa,

Try:

=IF (Influence@row = "High", 3, IF (Influence@row = "Medium", 2, IF (Influence@row = "Low", 1, IF (Influence@row = "None", 0))))

Your plan sounds good. Score each. Total score. Determine category. Using helper columns is a good technique to keep formulas simple. You can always hide them or move them to the far right out of sight.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

Glad you found a solution. Thank you for contributing to the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭

@Lisa Matthews2 , hey there!

I have some ideas, but I need to understand the impacts better to flesh them out. Does people vs process vs tech hold different weightings? Or is it 2 out of 3 means go here?

Not have a clear picture of that, I'd probably lean on the impacts from H, M, L or L/N to 9, 5, 3, 1, 0, and then I'd sum either mean or weight average them for an impact score. Then, the IF statement becomes something like this:

IF(AND(Influence="High",Impact>7),"Key Player")

Then you could start nesting If/And or If/Or to further refine your results.

If you converted your scores to numeric, then you could use IF with BETWEEN to isolate the middle ranges pretty simply.

That said, you could still use IF/AND with these definitions, it just long. It would look something like this:

IF(AND(Influence="High", [People Impact]="High",[Process Impact}="High",[Technology Impact]="High"),"Key Player",IF......

That can get cumbersome to write and the longer it gets, it can become tougher to debug, but it's very doable for a small matrix.

• ✭✭✭✭

Hello Malania!

Thank you so much for helping me!

I have some questions:

a. If the scores are converted to numeric, would you suggest that I insert some columns in between to record the numeric values? ... or can the conversion be written into the formula?

here is a description of how the numbers could be assigned:

Thank you, again!

--Lisa

• ✭✭✭✭✭

Hi, @Lisa Matthews2 ,

I generally just include a key that shows what the numeric values of each alpha are, and only allow a single value for each one:

High = 9

Med = 6

Low = 1

None = 0

Then you don't have deal with varying degrees of scaling. You could use your 3,2,1,0 scale, but it makes a quick visual review a little harder because the numbers are still in a small range. Using a larger spread, but single value means, as a prior manager told me, "people with consistently high impacts now have obscenely high numbers and it makes them easy to see in raw data." 36 isn't necessarily obscene, but when compared to 3, it's big enough to demonstrate significant impact.

You can write the numeric conversions into the formulas, but frankly, the more verbiose a formula is, the harder it can be to debug if you get an error, so I favor the helper column with numeric values that can be summed or counted. It's easier to teach to document; easier to teach to others; and if I were to be unavailable suddenly without a transition period, it's easier for someone who isn't a power user to follow along the logic. YMMV. We don't have a lot of power users in my workplace, so transition ease is always a consideration on legacy sheets.

• ✭✭✭✭

Hello, @Malania Hudson,

Thank you so much for helping me. I like your recommendation to assign numeric values and trying to move in that direction. I have haven't changed the values to the 9, 6, 1, 0 yet. I am using this formula to assign a number value but it is not working:

=IF (Influence@row = "High", 3, IF (Influence@row = "Medium", 2, IF (Influence@row = "Low", 1, IF (Influence@row = "None", 0)))

I am thinking that the next steps will be (?):

a) Add a column to include a formula which sums the values from the "impact" and "influence" columns

b) Add a column to include a formula which will return the value for "matrix position" (these ranges will be different once the 9, 6, 1, 0 set is used instead of the 3, 2, 1, 0 set)

9 to 12 = Key Player

5 to 8 = Keep Satisfied

1 to 4 = Keep Informed

0 = Monitor

Thanks, again, for looking at this. I'm struggling.

--Lisa

• ✭✭✭✭✭✭

Hi Lisa,

Try:

=IF (Influence@row = "High", 3, IF (Influence@row = "Medium", 2, IF (Influence@row = "Low", 1, IF (Influence@row = "None", 0))))

Your plan sounds good. Score each. Total score. Determine category. Using helper columns is a good technique to keep formulas simple. You can always hide them or move them to the far right out of sight.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭

Hello @Mark Cronk ,

The formula works! The community is a life-saver, I am so grateful.

Thank you,

Lisa

• ✭✭✭✭✭✭