Multiple IF Formula for Matrix

@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.

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.