Assigning Rank to Text and Creating Colour Scaling Accordingly

Smok3y

New Member
Joined
Apr 9, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I'm looking for a way to colour scale a table that contains text, based on rankings assigned to that text (essentially pairing ordinal ranks with nominal data, and carrying those ranks across as a means to provide colour scaling).

Example image attached - basically I want the table cells that contain higher ranked nominal data (in this case team names in the power rankings) to appear red and scale down from there (shades of red, orange, yellow and green).

Ideally this would entail a customisable solution, where the power rankings can change and table colour scaling updates accordingly from there.

Any help would be greatly appreciated :)

Cheers, Smok3y.
 

Attachments

  • Schedule Map.PNG
    Schedule Map.PNG
    111.2 KB · Views: 7

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
Possible, but you would have to create a rule for each rank. Something like this:

Book1.xlsm
LMNOPQRST
1xyzghiabcabc
21abcdefdefdef
32defghiabcxyz
43ghijkljkldefjkl
54jklxyzxyz
65xyz
Sheet7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q1:T5Expression=INDEX($L:$L,MATCH(Q1,$M:$M,0))=3textNO
Q1:T5Expression=INDEX($L:$L,MATCH(Q1,$M:$M,0))=2textNO
Q1:T5Expression=INDEX($L:$L,MATCH(Q1,$M:$M,0))=1textNO
 

Smok3y

New Member
Joined
Apr 9, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Possible, but you would have to create a rule for each rank. Something like this:

Book1.xlsm
LMNOPQRST
1xyzghiabcabc
21abcdefdefdef
32defghiabcxyz
43ghijkljkldefjkl
54jklxyzxyz
65xyz
Sheet7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q1:T5Expression=INDEX($L:$L,MATCH(Q1,$M:$M,0))=3textNO
Q1:T5Expression=INDEX($L:$L,MATCH(Q1,$M:$M,0))=2textNO
Q1:T5Expression=INDEX($L:$L,MATCH(Q1,$M:$M,0))=1textNO
Thanks so much for the reply.

I'm having trouble translating your example across to mine, not sure where I am going wrong.

Would this be in Conditional Formatting? What is the Stop If True column? Bit confused how this works if only the first table cell (Q1) is referenced in the formula.

Can you step this out if possible? Apologies I haven't come across one like this before.

Cheers, Smok3y.
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
Would this be in Conditional Formatting? What is the Stop If True column?
Yes, that's in conditional formatting. Stop If True is a checkbox column available when you look at "Manage rules". If checked, Excel will stop at the matching condition and will not keep looking at the conditions down the list for another match. Most of the time this is not necessary, unless you have possible overlapping conditions to check.

Bit confused how this works if only the first table cell (Q1) is referenced in the formula.
The referenced cell is relative (Q1), and not absolute ($Q$1), so the referenced relative cell is actually "moving" as you apply it to a range of cells.
If you reference A1 in a range of A1:B2 the referenced cell moves as Excel looks at B1, A2, B2, etc. Some examples of how the cell is being "changed" to for each type of referencing.
A1:
A1|B1
A2|B2

$A1:
A1|A1
A2|A2

A$1:
A1|B1
A1|B1

$A$1:
A1|A1
A1|A1


I'm having trouble translating your example across to mine, not sure where I am going wrong.
So to apply the formula to your sheet, you need to break down the formula first.
These are the 3 components to change:
=INDEX($L:$L,MATCH(Q1,$M:$M,0))=1

$L:$L should be your ranking column, which, according to the screenshot, is also on the column for "RD 5"
$M:$M should be your ranking column, which, according to the screenshot, is also on the column for "RD 6"
You probably can't reference the entire column here since your ranking data is placed below the main matrix. So it should look something like:
$L:$L -> $C$23:$C$40
$M:$M -> $D$23:$D$40
depending on what's your actual column/rows (your screenshot did not show columns and rows)

Q1 should be the top left cell of your matrix. If the matrix range is C3:U20, then change Q1 to C3.
 
Solution

Smok3y

New Member
Joined
Apr 9, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Yes, that's in conditional formatting. Stop If True is a checkbox column available when you look at "Manage rules". If checked, Excel will stop at the matching condition and will not keep looking at the conditions down the list for another match. Most of the time this is not necessary, unless you have possible overlapping conditions to check.


The referenced cell is relative (Q1), and not absolute ($Q$1), so the referenced relative cell is actually "moving" as you apply it to a range of cells.
If you reference A1 in a range of A1:B2 the referenced cell moves as Excel looks at B1, A2, B2, etc. Some examples of how the cell is being "changed" to for each type of referencing.
A1:
A1|B1
A2|B2

$A1:
A1|A1
A2|A2

A$1:
A1|B1
A1|B1

$A$1:
A1|A1
A1|A1



So to apply the formula to your sheet, you need to break down the formula first.
These are the 3 components to change:
=INDEX($L:$L,MATCH(Q1,$M:$M,0))=1

$L:$L should be your ranking column, which, according to the screenshot, is also on the column for "RD 5"
$M:$M should be your ranking column, which, according to the screenshot, is also on the column for "RD 6"
You probably can't reference the entire column here since your ranking data is placed below the main matrix. So it should look something like:
$L:$L -> $C$23:$C$40
$M:$M -> $D$23:$D$40
depending on what's your actual column/rows (your screenshot did not show columns and rows)

Q1 should be the top left cell of your matrix. If the matrix range is C3:U20, then change Q1 to C3.
Yep that worked great!

Thanks so much for your help, I really appreciate it! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

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