Coloring cells according to the value to which its content is closest [closed]

Question

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do.

Link

Example: if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row then find which number its Length(F2:F100) is closest to according to “8.97(#cc0000), 11.57(#b30080), 14.17(#9900ff), 16.76(#834ff5), 19.36(#6d9eeb)” coloring the cell accordingly.

Kinda like what conditional formatting's color scale does.

My explanation

Answer 1

Given the numbers

8.97
11.57
14.17
16.76
19.36

compute the midpoints between them (using the spreadsheet or otherwise):

10.27
12.87
15.465
18.06

If a number is less than 10.27, then the closest value to it is 8.97. Otherwise: if the number is less than 12.87, the closest is 11.57. Otherwise... and so on.

This logic is expressed in conditional formatting by creating five rules in the following order:

1. Value is less than 10.27: color (#cc0000)
2. Value is less than 12.87: color (#b30080)
3. Value is less than 15.465: color (#9900ff)
4. Value is less than 18.06: color (#834ff5)
5. Cell is nonempty: color (#6d9eeb)

The conditions are checked in the order stated, top to bottom. As soon as one condition holds, the corresponding format is applied and the formatting is done.

For example, the value of 13 will be colored #9900ff, which is correct.

You can store the midpoints in the spreadsheet itself, referring to their cells from the conditional formatting rules.

Answer 2

I think this is what you meant:

• Current item is THIS
• Select for all items whose A = this.A and whose B = this.B
• Check the rank of THIS in the list of all matched items.

The appropriate code, in your case is:

=match(F2, sort(filter(F$2:F,C$2:C=C2,I$2:I=I2)))

Let's break it down:

1. filter(F$2:F,C$2:C=C2,I$2:I=I2) gives you a list of all column F values whose corresponding C & I value matches C2 and I2, respectively.
2. sort(...) sort the list in ascending order (lowest number first)
3. match(F2, sort(...)) gives you the rank of F2 in the matched F value list

To set the color automatically

1. select F2:F100
2. select "Format" >> "Conditional Formatting..."
3. in "format cell if", choose "custom formula is"
4. in the formula field, put =match(F2, sort(filter(F$2:F,C$2:C=C2,I$2:I=I2)))=1
5. set the format style for the lowest ranked item

Repeat the steps, but change the last number of the formula to 2, 3, 4... and set the appropriate format style

Note the formula assumes that there will never be identical F values

If you want to rank by descending order (greatest number first), you need to change formula to the following:

=match(F2, sort(filter(F$2:F,C$2:C=C2,I$2:I=I2),1,FALSE),-1)

note:

sort(...) is now sort(..,1,FALSE), and match(...) is now match(...,-1)

See match, sort, and filter

This is close to what I was wanting so I made a Sheet that will hopefully show you what I mean, since i'm very bad at explaining xD, Link