Basic Impact-Effort Matrix

mitzihowe

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am looking to build a simple Impact-Effort Matrix graph. After extracting some survey data, I run a macro to populate a table as such:

High ImpactLow ImpactHigh EffortLow Effort
Project 15380
Project 25362
Project 34408
Project 42635
Project 53580
Project 63544
Project 74435
Project 84444
Project 95353
Project 104444

This is based on a sample size of 8 voters who rate each project on whether a project is high/low in terms of effort and impact.

A project is rated High Impact (or whichever) as long as >50% votes are received. Hence, Project 1 is rated as High Impact and High Effort.

I want to be able to represent this data into a quadrant that just simply shows the project name in the quadrant it is supposed to be in. Something like this (the axes would be effort and impact:

Project 1 Project 2 Project 9
Project 4 Project 5

Is there some way to do it? Apologies for the longish query and thanks in advance for any advise.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi,
welcome to the forum.
This uses a few helper columns but eventually puts the project names into a cell quadrant.
I have ignored results that are 4 & 4 (yellow cells), The expected results you showed indicated that.
Hope this helps
Paul.

Book1
ABCDEFGHI
1High ImpactLow ImpactHigh EffortLow EffortImpactEffortQuadrant
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low 
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low  
8Project 74435 Low 
9Project 84444   
10Project 95353HighHighA
11Project 104444   
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9 
16Low ImpactCDLow ImpactProject 5Project 4
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16F15=TEXTJOIN(CHAR(10),TRUE, IF(B15=$I$2:$I$11, $A$2:$A$11, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.


Edit - Cell F15 should have "wrap text" format
 

mitzihowe

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi,
welcome to the forum.
This uses a few helper columns but eventually puts the project names into a cell quadrant.
I have ignored results that are 4 & 4 (yellow cells), The expected results you showed indicated that.
Hope this helps
Paul.

Book1
ABCDEFGHI
1High ImpactLow ImpactHigh EffortLow EffortImpactEffortQuadrant
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low 
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low  
8Project 74435 Low 
9Project 84444   
10Project 95353HighHighA
11Project 104444   
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9 
16Low ImpactCDLow ImpactProject 5Project 4
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16F15=TEXTJOIN(CHAR(10),TRUE, IF(B15=$I$2:$I$11, $A$2:$A$11, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.


Edit - Cell F15 should have "wrap text" format
Thanks so much, Taul!

Unfortunately, after entering the array formula I get a #NAME? error...
 

mitzihowe

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi
Hi,
welcome to the forum.
This uses a few helper columns but eventually puts the project names into a cell quadrant.
I have ignored results that are 4 & 4 (yellow cells), The expected results you showed indicated that.
Hope this helps
Paul.

Book1
ABCDEFGHI
1High ImpactLow ImpactHigh EffortLow EffortImpactEffortQuadrant
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low 
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low  
8Project 74435 Low 
9Project 84444   
10Project 95353HighHighA
11Project 104444   
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9 
16Low ImpactCDLow ImpactProject 5Project 4
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16F15=TEXTJOIN(CHAR(10),TRUE, IF(B15=$I$2:$I$11, $A$2:$A$11, ""))
Press CTRL+SHIFT+ENTER to enter array formulas.


Edit - Cell F15 should have "wrap text" format
Hi Taul,

I just realised it is because I'm using Excel 2016 and it has no TEXTJOIN function. Gah!

Really appreciate your help on this. If I just had that function life will be perfect...
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi,
My apologies, I mistakenly thought TEXTJOIN existed in the 2016 version, my bad. I actually did a google search to check and I mis-read it.

As an alternative, there is a UDF (User Defined Function) available. Rick Rothstein has kindly produced one, the details are here Listed in post #2: -
Vlookup multiple Values then Concatenate all values in One cell

Also here at Ricks Rothstein’s Corner
LookUp Value and Concatenate All Found Results

I have used the UDF from his first post but I would suggest you read through the full post to see the discussion and how it has developed.
There are two Named Ranges:- SearchRange and ReturnRange, I have indicated this as a blue header over columns “A” and “I”
The formula in cell F15 is
Code:
 =LookUpConcat(B15,SearchRange,ReturnRange)
The search item is the letter “A” in cell B15

If you wish, you can hard code the Quadrant letter “A” in the formulas like this
Code:
 =LookUpConcat(“A”,SearchRange,ReturnRange)

I have ignored the optional parameters but you can include them if you wish.
The VBA code needs to go into a standard module.
The VBA code is:-

I have added & Chr(10) to Rick's code (in red text below) this is to wrap the test.
ReturnVal = ReturnRange(X).Value & Chr(10)

Code:
Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value & Chr(10)
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
  
End Function

Forum - Graph projects.xlsx
ABCDEFGHI
1ReturnRangeHigh ImpactLow ImpactHigh EffortLow EffortImpactEffortSearchRange
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low 
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low  
8Project 74435 Low 
9Project 84444   
10Project 95353HighHighA
11Project 104444   
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9  
16Low ImpactCDLow ImpactProject 5 Project 4
Test 2
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16F15=LookUpConcat(B15,SearchRange,ReturnRange,"")
Named Ranges
NameRefers ToCells
ReturnRange='Test 2'!$A$2:$A$11F15:G16
SearchRange='Test 2'!$I$2:$I$11F15:G16
 
Solution

mitzihowe

New Member
Joined
Dec 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi,
My apologies, I mistakenly thought TEXTJOIN existed in the 2016 version, my bad. I actually did a google search to check and I mis-read it.

As an alternative, there is a UDF (User Defined Function) available. Rick Rothstein has kindly produced one, the details are here Listed in post #2: -
Vlookup multiple Values then Concatenate all values in One cell

Also here at Ricks Rothstein’s Corner
LookUp Value and Concatenate All Found Results

I have used the UDF from his first post but I would suggest you read through the full post to see the discussion and how it has developed.
There are two Named Ranges:- SearchRange and ReturnRange, I have indicated this as a blue header over columns “A” and “I”
The formula in cell F15 is
Code:
 =LookUpConcat(B15,SearchRange,ReturnRange)
The search item is the letter “A” in cell B15

If you wish, you can hard code the Quadrant letter “A” in the formulas like this
Code:
 =LookUpConcat(“A”,SearchRange,ReturnRange)

I have ignored the optional parameters but you can include them if you wish.
The VBA code needs to go into a standard module.
The VBA code is:-

I have added & Chr(10) to Rick's code (in red text below) this is to wrap the test.
ReturnVal = ReturnRange(X).Value & Chr(10)

Code:
Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                  
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
 
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value & Chr(10)
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
   
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
 
End Function

Forum - Graph projects.xlsx
ABCDEFGHI
1ReturnRangeHigh ImpactLow ImpactHigh EffortLow EffortImpactEffortSearchRange
2Project 15380HighHighA
3Project 25362HighHighA
4Project 34408 Low 
5Project 42635LowLowD
6Project 53580LowHighC
7Project 63544Low  
8Project 74435 Low 
9Project 84444   
10Project 95353HighHighA
11Project 104444   
12
13
14High effortLow EffortHigh EffortLow Effort
15High ImpactABHigh ImpactProject 1 Project 2 Project 9  
16Low ImpactCDLow ImpactProject 5 Project 4
Test 2
Cell Formulas
RangeFormula
G2:G11G2=IF(B2=C2,"",IF(B2>C2,"High","Low"))
H2:H11H2=IF(D2=E2,"",IF(D2>E2,"High","Low"))
I2:I11I2=IF(OR(G2="",H2=""),"",IF(AND(G2="High",H2="High"),"A",IF(AND(G2="High",H2="Low"),"B",IF(AND(G2="Low",H2="High"),"C","D"))))
F15:G16F15=LookUpConcat(B15,SearchRange,ReturnRange,"")
Named Ranges
NameRefers ToCells
ReturnRange='Test 2'!$A$2:$A$11F15:G16
SearchRange='Test 2'!$I$2:$I$11F15:G16
Hi Taul,

No worries. I actually found a workaround on simulating TextJoin in Excel 2016 and it works a treat.

But most of all, THANK YOU SO MUCH for your help. It was the exact solution I was looking for and you really helped a ton. I am so grateful.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

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
Top