Impact Effort Matrix

[Brew]

Hello all

I need help with my Impact Effort Matrix. The records results in column C and D starting at row 5 is my Impact and Effort results. see the sample chart below.

I would like to do 2 things

How do I create a formula for my chart starting F5 where it will address all of the following conditions and give me the appropriate results and add a color for condition and its matching results in column F:
If C= 4 or 5 and D=4 or 5, then HighImpact-Hard
If C= 1 or 2 and D=4 or 5, then LowImpact-Hard
If C= 4 or 5 and D=1 or 2, then HighImpact-Easy
If C= 1 or 2 and D=1 or 2, then LowImpact-Easy
If C= 3 and D=1 or 2, then NeutralImpact-Easy
If C= 3 and D=4 or 5, then NeutralImpact-hard
If C= 4 or 5 and D=3, then HighImpact-NeutralEffort
If C= 1 or 2 and D=3, then LowImpact-NeutralEffort

Also, to the right of my table B5 to E94 is there a way to create a 4 quadrant plot, where the top left is the highImpact-hard, the bottom left= LowImpact-hard, the top right=highimpact-Easy, the bottom right= lowimpact-Easy and place the cell number in column B that matches the records condition in column C&D into the appropriate quadrant. row 5 is the first record. row 4 is the heading row.

Ideas Impact Effort Total
1) Buy Local and Direct from Producers 5 3 8
2) Share your Knowledge & Enthusiasm for Local Food 3 3 6
3) Join a Community Supported Agriculture (CSA) Program 1 5 6
4) Network Home Gardeners 5 2 7
5) Add Value to Your Own Agricultural Products 4 3 7
6) Identify Opportunities for Local Distribution 5 4 9
7) Find Ways to Pool Resources & Costs with Others 5 3 8
8) Network Community-Based Farmers 5 3 8
9) Create Incentives for Consumers to Shop Locally 5 2 7

 

[Brew]

I made an error in my conditions that i need to generate the formula for. It should be the following:

If C= 4 or 5 and D=4 or 5, then HighImpact-Easy
If C= 1 or 2 and D=4 or 5, then LowImpact-Easy
If C= 4 or 5 and D=1 or 2, then HighImpact-Hard
If C= 1 or 2 and D=1 or 2, then LowImpact-Hard
If C= 3 and D=1 or 2, then NeutralImpact-Hard
If C= 3 and D=4 or 5, then NeutralImpact-Easy
If C= 4 or 5 and D=3, then HighImpact-NeutralEffort
If C= 1 or 2 and D=3, then LowImpact-NeutralEffort

 

[dreid1011]

Perhaps this for the first part:

=IF(AND(OR(C5=4,C5=5),OR(D5=4,D5=5)),"HighImpact-Easy",IF(AND(OR(C5=1,C5=2),OR(D5=4,D5=5)),"LowImpact-Easy",IF(AND(OR(C5=4,C5=5),OR(D5=1,D5=2)),"HighImpact-Hard",IF(AND(OR(C5=1,C5=2),OR(D5=1,D5=2)),"LowImpact-Hard",IF(AND(C5=3,OR(D5=1,D5=2)),"NeutralImpact-Easy",IF(AND(C5=3,OR(D5=4,D5=5)),"NeutralImpact-Hard",IF(AND(D5=3,OR(C5=4,C5=5)),"HighImpact-NeutralEffort",IF(AND(D5=3,OR(C5=1,C5=2)),"LowImpact-NeutralEffort",""))))))))

 

[Brew]

Yes dreid1011 thanks, that is correct for the first part of the problem, however, how do i add the following condition to this formula:

If C= 3 and D=3, then Neutral-Neutral

 

[dreid1011]

At the very end of the formula where it shows "", replace the "" with this:

IF(AND(C5=3,D5=3),"Neutral-Neutral","")

 

[Brew]

the record in row 6 is a match but the updated formula gives no result. below is the updated formula this record:
=IF(AND(OR(C6=4,C6=5),OR(D6=4,D6=5)),"HighImpact-Easy",IF(AND(OR(C6=1,C6=2),OR(D6=4,D6=5)),"LowImpact-Easy",IF(AND(OR(C6=4,C6=5),OR(D6=1,D6=2)),"HighImpact-Hard",IF(AND(OR(C6=1,C6=2),OR(D6=1,D6=2)),"LowImpact-Hard",IF(AND(C6=3,OR(D6=1,D6=2)),"NeutralImpact-Easy",IF(AND(C6=3,OR(D6=4,D6=5)),"NeutralImpact-Hard",IF(AND(D6=3,OR(C6=4,C6=5)),"HighImpact-NeutralEffort",IF(AND(D6=3,OR(C6=1,C6=2)),"LowImpact-NeutralEffort",IF(AND(C5=3,D5=3),"Neutral-Neutral","")))))))))

also, how do i make each condition a different color in the result cell?

 

[dreid1011]

It looks like your cell reference is off by one. When I proposed the addition, I assumed the formula was going into row 5 again. The updated formula you have is looking at row 6. So, change the last part to C6 and D6 for that formula.

For the coloring, you could use conditional formatting.

You would select the range F5 to the last used row, then create the following formulas in Conditional Formatting:

=F5="HighImpact-Easy"

Repeat this formula for each possible result (9 in total), each with their own color.

 

[Brew]

OK thanks Dreid1011, the formula works, however, for the conditional formatting i have a window 7 and the conditional formatting only allows me to enter one equal to condition and i have 9, how do i enter all 9 to get 9 different matching colors

 

[dreid1011]

You need to make a new rule for each one. So 9 rules in total.

 

[Brew]

got it that works, thanks Dried1011 for your assistance.

Are you able to provide assistance with the last part of the original problem:

Also, to the right of my table B5 to E94 is there a way to create a 4 quadrant plot, where the top left is the highImpact-hard, the bottom left= LowImpact-hard, the top right=highimpact-Easy, the bottom right= lowimpact-Easy and place the cell number in column B that matches the records condition in column C&D into the appropriate quadrant. row 5 is the first record. row 4 is the heading row. Starting in column K.