Impact Effort Matrix

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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
 
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.
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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:

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Could you provide an example of how the plot should look?
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
,,,,,,,,,,,,,,,,,,,,,,Hard,,,Hard,,,NeutralEffort,,,Easy,,,Easy
highimpact
highimpact
neutralimpact
lowimpact
lowimpact
 
Upvote 0
.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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top