Good day, the hours spent trying using one formula in Excel to find the smallest number and the biggest, and shaded using conditional formatting.

Without using IF, and in one formula.

I tried this:

painted only the smallest number of all.

Without using IF, and in one formula.

I tried this:

`=And(MAX($A$1:$A$20)=A1;MIN($A$1:$A$20))`

painted only the smallest number of all.

asked July 2nd 19 at 13:09

2 answers

answered on

Solution

Here is what worked:

`=OR(LOWER($C$2:$C$26;1)=C2;the LARGEST($C$2:$C$26;1)=C2)`

answered on July 2nd 19 at 13:13

need (fix Your formula **in bold**)

=And**IF**(MAX($A$1:$A$20)=A1;MIN($A$1:$A$20)**=A1**)

In your case, the rule will apply provided that A1 (cell A1) and simultaneously the maximum and the minimum (rule logical And), and this is possible only under the condition that all numbers in the range are equal.

In my version if the minimum or the maximum (logical OR)

In General, the standard rules of conditional formatting select the first 10 elements for the maximum and the last 10 elements for the minimum and set the number of desired values =1

Thus rendered the smallest and largest

According to the first embodiment of the screen rules:

According to the second embodiment, too, it all works:

=And

In your case, the rule will apply provided that A1 (cell A1) and simultaneously the maximum and the minimum (rule logical And), and this is possible only under the condition that all numbers in the range are equal.

In my version if the minimum or the maximum (logical OR)

In General, the standard rules of conditional formatting select the first 10 elements for the maximum and the last 10 elements for the minimum and set the number of desired values =1

Thus rendered the smallest and largest

According to the first embodiment of the screen rules:

According to the second embodiment, too, it all works:

Find more questions by tags Excel

PS and when you create a rule you have set up the fill in button format? if not, the rule works just the formatting is not configured - skye.Satterfield72 commented on July 2nd 19 at 13:22