SOLVED
Home

Conditional Formatting - Consider Only Value as Lowest Value

%3CLINGO-SUB%20id%3D%22lingo-sub-238250%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20-%20Consider%20Only%20Value%20as%20Lowest%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-238250%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20%22Formatting%20Cells%20Based%20on%20Value%22%20if%20there%20is%20only%20one%20cell%20with%20data%20out%20of%20a%20set%20of%20otherwise%20blank%20cells%2C%20the%20rule%20considers%20the%20existent%20data%20as%20highest%20value.%20This%20makes%20sense%20logically%2C%20but%20I%20want%20the%20lowest%20value%2C%20even%20if%20it%20is%20the%20only%20value%2C%20to%20display%20as%20such.%20I%20can't%20figure%20out%20how%20to%20use%20another%20rule%20to%20eliminate%20the%20blank%20cells%20from%20the%20value-based%20rule.%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-238250%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eblank%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eblank%20cells%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Econditions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERules%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evalue%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-238507%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Consider%20Only%20Value%20as%20Lowest%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-238507%22%20slang%3D%22en-US%22%3EI'll%20be%20honest%2C%20I%20don't%20understand%20how%20the%20above%20works%2C%20mostly%20due%20to%20the%20(COUNTA(x%2Cy%2Cz)%3D1)%20output%20being%20either%20TRUE%20or%20FALSE%20-%20I'm%20not%20sure%20how%20that%20affects%20the%20minimum%20formula.%20I%20also%20don't%20know%20how%20calling%20out%20the%20largest%20number%20in%20MIN%20and%20the%20smallest%20in%20MAX%20isn't%20reversing%20the%20color%20gradient%20(without%20my%20reversing%20the%20colors).%3CBR%20%2F%3E%3CBR%20%2F%3EThat%20said%2C%20it%20works%20beautifully.%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-238312%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20-%20Consider%20Only%20Value%20as%20Lowest%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-238312%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ben%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20exactly%20blank%20cells%20are%20ignored%20in%20any%20case.%20If%20you%20have%20only%20one%20value%20it%20is%20simultaneously%20min%20and%20max%20value%20in%20the%20range%2C%20Excel%20goes%20from%20min%20to%20max%20and%20shows%20you%20that%20one%20number%20as%20the%26nbsp%3Bbiggest%20one.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you'd%20like%20to%20show%20it%20as%20the%20lowest%20you%20shall%20reverse%20%22the%20axis%22%20and%20if%20that%20is%20the%20only%20number%20add%20to%20it%20something%20compare%20reversed%20minimum%2C%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20596px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F44308i3B93422E5011187C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhere%20formula%20for%20minimum%20is%3C%2FP%3E%0A%3CPRE%3E%3DMAX(%24A%241%3A%24A%2410)%2B(COUNTA(%24A%241%3A%24A%2410)%3D1)%3C%2FPRE%3E%0A%3CP%3Eand%20for%20maximum%3C%2FP%3E%0A%3CPRE%3E%3DMIN(%24A%241%3A%24A%2410)%3C%2FPRE%3E%0A%3CP%3EIf%20you%20have%20more%20than%20one%20number%20colour%20grade%20will%20be%20correct%20(min%20is%20darker%20for%20our%20scale)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%2086px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F44309i1F1D85C0166CEE77%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Ben Smith
New Contributor

When "Formatting Cells Based on Value" if there is only one cell with data out of a set of otherwise blank cells, the rule considers the existent data as highest value. This makes sense logically, but I want the lowest value, even if it is the only value, to display as such. I can't figure out how to use another rule to eliminate the blank cells from the value-based rule. Thanks in advance!

2 Replies
Highlighted
Solution

Hi Ben,

 

More exactly blank cells are ignored in any case. If you have only one value it is simultaneously min and max value in the range, Excel goes from min to max and shows you that one number as the biggest one.

 

If you'd like to show it as the lowest you shall reverse "the axis" and if that is the only number add to it something compare reversed minimum, like this

image.png

where formula for minimum is

=MAX($A$1:$A$10)+(COUNTA($A$1:$A$10)=1)

and for maximum

=MIN($A$1:$A$10)

If you have more than one number colour grade will be correct (min is darker for our scale)

image.png

and attached

Highlighted
I'll be honest, I don't understand how the above works, mostly due to the (COUNTA(x,y,z)=1) output being either TRUE or FALSE - I'm not sure how that affects the minimum formula. I also don't know how calling out the largest number in MIN and the smallest in MAX isn't reversing the color gradient (without my reversing the colors).

That said, it works beautifully. Thank you!
Related Conversations
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Relative conditional formatting
melissach in Excel on
8 Replies
if then formula help
BMARSH99 in Excel on
1 Replies