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]

Thanks Dried1011, this is very close to what i am looking for just 2 adjustments need.

1) Because the content in column B are sentences and not a single alphabet can you assign a the row number of the matching record as the output of the plot result.

2) the placement of the plot results should be modified, for example with high Impact-Easy quadrant the result placements within the quadrant should be as follows:
C column should be the up and down result
D column should be the left - right result
so
If 5-5, then Top far right of the quadrant,
If 5-4, then upper top more towards the left-right center of the quadrant
If 5-3, then upper top more towards the far left of the quadrant
The other possibilities within this quadrant would be slight offsets from the above conditions, apply this rule to all of the other quadrants.
note: we should have spacing for duplicate results so that they do not occupied the exact same space and we can see each one.

 

[dreid1011]

Thanks Dried1011, this is very close to what i am looking for just 2 adjustments need.

1) Because the content in column B are sentences and not a single alphabet can you assign a the row number of the matching record as the output of the plot result.

You would like the text on the plot to read B5, B6, B7 etc?

2) the placement of the plot results should be modified, for example with high Impact-Easy quadrant the result placements within the quadrant should be as follows:
C column should be the up and down result
D column should be the left - right result
so
If 5-5, then Top far right of the quadrant,
If 5-4, then upper top more towards the left-right center of the quadrant
If 5-3, then upper top more towards the far left of the quadrant
The other possibilities within this quadrant would be slight offsets from the above conditions, apply this rule to all of the other quadrants.
note: we should have spacing for duplicate results so that they do not occupied the exact same space and we can see each one.

Currently it is a manual drag/move. I will look into making it auto populate the plot.

How many potential duplicates can there be? This is my first venture into creating text boxes with vba, so I'm learning as I go.

Edit: Is it 1-5 for each quadrant or 1-5 spanning 2 quadrants with 3 in the middle? (3-3 would be center of the grid where Y is in the image)

 

[Brew]

You would like the text on the plot to read B5, B6, B7 etc? Yes

Currently it is a manual drag/move. I will look into making it auto populate the plot. Ok great, if not i will adjust to updating it manually

How many potential duplicates can there be? This is my first venture into creating text boxes with vba, so I'm learning as I go. ok not sure, probably under 20

Edit: Is it 1-5 for each quadrant or 1-5 spanning 2 quadrants with 3 in the middle? It is 1-5 for each quadrant and 3 is in the middle. (3-3 would be center of the grid where Y is in the image) Yes is in the center of the grid, however, if there are duplicates they would have to be offset slightly from that exact point.

 

[dreid1011]

You would like the text on the plot to read B5, B6, B7 etc? Yes

Currently it is a manual drag/move. I will look into making it auto populate the plot. Ok great, if not i will adjust to updating it manually

How many potential duplicates can there be? This is my first venture into creating text boxes with vba, so I'm learning as I go. ok not sure, probably under 20

Edit: Is it 1-5 for each quadrant or 1-5 spanning 2 quadrants with 3 in the middle? It is 1-5 for each quadrant and 3 is in the middle. (3-3 would be center of the grid where Y is in the image) Yes is in the center of the grid, however, if there are duplicates they would have to be offset slightly from that exact point.

Ok, so.. Like this:

5	4	3	2	1	1	2	3	4	5
4									4
3		3					3		3
2									2
1				1	1				1
1				1	1				1
2									2
3		3					3		3
4									4
5	4	3	2	1	1	2	3	4	5

<tbody>
</tbody>

 

[dreid1011]

As I look at the results of the formula... the plot I posted above doesn't make sense. Which quadrant would the Neutrals go into?

This makes more sense to include the neutrals, except this doesn't exactly match to the results either:

5	4	3	2	1
4
3		3
2
1				1

<tbody>
</tbody>

Could you provide an example of how the plot should look?

 

[Brew]

I just created a sample plot with results but it wont keep the format:

so i am not sure you can follow it but here it is just in case:

hard hard neutraleffort easy easy
1 2 3 4 5
highimpact 55-1x 5-5x 5 highimpact
highimpact4 4-2x 4-4x 4 highimpact
neutralimpact33-2x 3-3x 3 neutralimpact
lowimpact 2 2-1x 2-3x 2 lowimpact
lowimpact 1 1-5x 1 lowimpact
1 2 3 4 5
hard hard neutraleffort easy easy

 

[Brew]

I just created a sample plot with results but it wont keep the format:

so i am not sure you can follow it but here it is just in case:

hard hard neutraleffort easy easy
1 2 3 4 5
highimpact 55-1x 5-5x 5 highimpact
highimpact4 4-2x 4-4x 4 highimpact
neutralimpact33-2x 3-3x 3 neutralimpact
lowimpact 2 2-1x 2-3x 2 lowimpact
lowimpact 1 1-5x 1 lowimpact
1 2 3 4 5
hard hard neutraleffort easy easy

.1,2,3,4,5
5,,,,,,,,,,,,5
4,,,,,,,,,,,,4
3,,,,,,,,,,,,3
2,,,,,,,,,,,,2
1,,,,,,,,,,,,1
.1,2,3,4,5

3 is the x & y axis

 

[Brew]

,,,,,,,,,,,,,,,,,,,,,,Hard,,,Hard,,,NeutralEffort,,,Easy,,,Easy
highimpact
highimpact
neutralimpact
lowimpact
lowimpact

 

[dreid1011]

.1,2,3,4,5
5,,,,,,,,,,,,5
4,,,,,,,,,,,,4
3,,,,,,,,,,,,3
2,,,,,,,,,,,,2
1,,,,,,,,,,,,1
.1,2,3,4,5

3 is the x & y axis

Ok, that helps.

For the moment I have a mostly working bit of code, but it can only handle one duplicate for each point. After the first duplicate, the others will just keep stacking up on top of each other.

Edit: Ok, it can now handle multiple duplicates, but after about 4 they start to run into other points. I'm not sure how to go about offsetting them in a pattern that won't run into other points so quickly.

 

[Brew]

Well if there is a way for me to select record in column b and it auto-selects the matching plot result, then i can manually move that plot result to where it it is visible within the appropriate quadrant. Can you do setup that condition.