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

[DarrenK]

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:

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)

 

[StephenCrump]

Not totally clear, but heading in the right direction perhaps?

	A	B	C	D	E
1	Priorities
2	Top
3	High
4	Medium		100%		Answer with Top priority
5	Sluggish
6	Zero		60%		Answer with Medium priority
7
8			30%		Answer with Zero priority
9
10			60%		Answer with Medium priority
11
12			90%		Answer with High priority
Sheet1

Cell Formulas
Range	Formula
E4,E12,E10,E8,E6	E4	="Answer with " & INDEX(Priorities,RANK.EQ(C4,C$4:C$12)) & " priority"

Named Ranges
Name	Refers To	Cells
Priorities	=Sheet1!$A$2:$A$6	E12, E10, E8, E6, E4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:C12	Other Type	Color scale		NO

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.

 

[DarrenK]

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.

 

[StephenCrump]

You're welcome. I'm glad we could help.