Let's say you have a table containing cells that have values generated from a formula. The value in those cells all start with a number (until the separation with a ";" ). Now you'd like to apply a range of colors to the cells on this table, based on how high this number is.
Is it possible to have such a formula in conditional formatting ?
Sheet used for test : https://docs.google.com/spreadsheets/d/1MUkYDPoR1NxB8qWcYr_2Fp91FgUbnUOpfGd7EUuWCOg/edit?usp=sharing
at this time color scale of conditional formatting can not be set to your B2:D5 range due to its limitation. if there are not many values in your dataset you can use regular conditional formatting where you can use MAX and MIN and then LARGE and SMALL to return 2nd, 3rd, etc. a lot of setting it up but it would work.
example:
=MAX(INDEX(REGEXEXTRACT(B$2:B$5; "(.*);")*1))=REGEXEXTRACT(B2; "(.*);")*1
=MIN(INDEX(REGEXEXTRACT(B$2:B$5; "(.*);")*1))=REGEXEXTRACT(B2; "(.*);")*1
=LARGE(INDEX(REGEXEXTRACT(B$2:B$5; "(.*);")*1); 2)=REGEXEXTRACT(B2; "(.*);")*1
=SMALL(INDEX(REGEXEXTRACT(B$2:B$5; "(.*);")*1); 2)=REGEXEXTRACT(B2; "(.*);")*1
