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

 

[dreid1011]

I'll see if I can figure something out for that, sure.

 

[Brew]

Alright thanks Dried1011

 

[dreid1011]

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

Is this a sample of your data? How is it interpreted?

Also, with the graph/plot, is it possible to have duplicates in the list in B, C, D? And if so, would you want all the occurrences appear in the plot?

 

[Brew]

Is this a sample of your data? Yes How is it interpreted? as cell numbers from the B column

Also, with the graph/plot, is it possible to have duplicates in the list in B, C, D? all matches from column F will result in the same quadrant. And if so, would you want all the occurrences appear in the plot? B5, B6, B7,....B94 and the plot starts in column K
and when you mouse over each plot result it should highlight the matching cell in column B.

I want to be like the concept in the link below
https://www.youtube.com/watch?v=_grj-UKUAVM

 

[Brew]

UPDATE
Is this a sample of your data? Yes How is it interpreted? as cell numbers from the B column

Also, with the graph/plot, is it possible to have duplicates in the list in B, C, D? all matches from column F will result in the same quadrant. And if so, would you want all the occurrences appear in the plot? B5, B6, B7,....B94 and the plot starts in column K
and when you mouse over each plot result it should highlight the matching cell content in column B. Also, have an overall background color in column B for each quadrant results.

I want to be like the concept in the link below
https://www.youtube.com/watch?v=_grj-UKUAVM

 

[dreid1011]

I was attempting to post this before lunch, but take a look at this:

https://imgur.com/gallery/e7k9mNU

Is this heading in the right direction? I don't know if I can do any fancy highlighting column B based on what cell you mouse over in the plot. Also, I can't look at that video until I get home probably.

 

[Brew]

not quite, I think the video will help clear it up. it is a pretty good with a few modifications. If you cant do the move-over then maybe if you click on the plot result, will select the cell record in column B or highlight the record in column A, something like that

 

[dreid1011]

Ok, I watched the video and I think I see what you are after. Do you want a manual drag and drop for each item so you can place them in the chart where you want? Or were you thinking of something automatic based on the formula/scoring results?

Is the list in column B permanent? Or will the items change over time? Will the list always be the same length?

 

[dreid1011]

Take a look here, https://imgur.com/gallery/1bdUCsg. Is that closer to what you want? The items are movable text boxes. I am using a bit of vba to create the text boxes on demand based on the length of column B.

 

[dreid1011]

https://imgur.com/a/1bdUCsg