Numbers: conditional formatting multiple values for one style

Hi hoopsmiller,

Insert some extra columns into the table where you can perform the IFs. Then you will end up with fewer rules for Conditional Highlighting. Here is an example of combining two or more IFs with the OR function.

Formula in B2 IF(OR($A2=3,$A2=8),$A2,"")

Formula in C2 IF(OR($A2=1,$A2=2),$A2,"")

Repeat for other IF OR comparisons.

Conditional Highlighting in Column A:

3 or 8, 1 or 2 reduced to two Conditional Highlighting rules.

Regards,

Ian.

"It seems like there should be a way to add multiple values for the same condition, "

It may 'seem like' there should, but it 'is that' there's not.

Conditional highlighting rules compare the value in the cell to be highlighted with another value. The other value may be written into the rule, or may be contained in another cell.

With 20 possible number values and five styles, you'll need a formula that can place one of five different values in the 'partner' cell that will provide the value to be compared with the value in the cell to be highlighted. Each comparison must make (at least) one if the comparisons return True, but must not be evaluated before another comparison which would also return true, but for a narrower range of values.

CH rules for number values offer a limited range of choices:

With a list of the 20 values, sorted into sets of numbers that are to be highlighted with the same style, it might be possible to compose a working set of rules and supporting formulas. Without that, it's a game of darts in the dark.

Regards,

Barry

Nicely done, Ian!

I played with this a bit to extend it to five columns to match hoopsmiller's description of '20 values and five styles', and tried a different means of getting the numbers of each style into the proper column.

Row 1 contains a list of the numbers in each set, separated by commas and with leading zeroes for the single digit numbers to make them all the same length. The cells are formatted as Text (before entering the values) to ensure retention of the leading zeroes.

The formula, set in B2, then filled down to row 21 and right to column E, is shown below:

B2: IF(ISERROR(FIND(RIGHT(0&$A2,2),B$1)),"",$A2)

Right(0&$A2,2) joins a 0 and the numeral(s) in A2 into a single text string, then takes the two rightmost characters of that string. (needed to make a consistent 2 character string to FIND)

FIND then attempts to locate the 2 character string in the longer string in row 1 of column B.

IF it fails to find a matching string, FIND throws an error, and ISERROR returns TRUE; if it does find the two character string, Find returns a number, which is not an error, so ISERROR returns False

IF gets the TRUE value from ISERROR and inserts a null string in the cell, or gets the FALSE value from ISERROR and puts the number from A2 in the cell.

The Conditional Highlighting rules for cells in column A are the same ones as used in Ian's solution, with three more added to

apply different fills for a match with the values in the aded columns.

Interesting exercise. Thanks, hoopsmiller, for the question, and Good on'ya, Mate, to Ian for the solution!

Regards,

Barry

Great solution, Barry!

Your approach allows editing of the various combinations of values in B1:F1 without the need to edit any "hard-wired" formulas.

I always enjoy studying and learning from your responses.

Regards,

Ian.