Concatenating cell value to include C.F. and GREATER/LESS THAN in relation to cell color

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hello,

Not certain this can be done but wanted to reach out before I wrote it off.

I have 5 cells (C4,C6,C8,C10,C12) that have percentages. Those are conditionally formatted with the Green-Yellow-Red Color Scale. If there are 5 different values in the cells, it displays the following colors:
1631731591585.png

In the adjacent cell to the left (B4,B6,B8,B10,B12), there are department names listed. For ease of understanding for anyone else viewing the file, I used the concatenate formula in the adjacent cells to the right (Column D):
=CONCAT("iF "&B4& "department gets a call, it will be answered with the " &(random cell)& " priority.")

In the random cell, I was trying to get the colors of the cells to work along with IF statements so that whichever of those 5 cells has the highest percentage, it adds which priority will be assigned into the concatenated formula. (Dark green would be HIGHEST, Red would be LOWEST. With the other 3 colors somewhere in between on the spectrum (SECOND HIGHEST, MEDIUM, SECOND LOWEST)) .

I may be overcomplicating it, but is there a way to utilize C.F. color along with IF statements to help determine which of the cells would have the highest value all the way to the lowest value? Or would it be easier to just use multiple IF statements to compare all 5 cells' values and come to the same result? And then with those results, determine which of 5 priority values would be populated in the concatenated statement? I'm really bad with embedded IF statements and would need help.

(EX: If dept 1 on top had the lowest of the 5 percentages, it's corresponding D cell would have the formula along with LOWEST priority. But then if 2 departments had the same percentage, their D formula would display the same value)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,346
Office Version
  1. 365
Platform
  1. Windows
Not totally clear, but heading in the right direction perhaps?

Cell Formulas
RangeFormula
E4,E12,E10,E8,E6E4="Answer with " & INDEX(Priorities,RANK.EQ(C4,C$4:C$12)) & " priority"
Named Ranges
NameRefers ToCells
Priorities=Sheet1!$A$2:$A$6E12, E10, E8, E6, E4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:C12Other TypeColor scaleNO

At the moment, we have 5 scores and 5 priorities, but if you like, this could easily be generalised for n scores, m priorities, n<>m.
 
Solution

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Stephen you're amazing! That's exactly what I need it to do. Thank you a great deal!!!

And it eliminates the need for a VBA code which I was trying really hard to steer clear of.
 

Forum statistics

Threads
1,147,482
Messages
5,741,409
Members
423,657
Latest member
Medrok2021

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