Aug 27 2018 01:50 PM
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!
Aug 27 2018 03:53 PM
SolutionHi 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
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)
and attached
Aug 28 2018 06:05 AM
Aug 27 2018 03:53 PM
SolutionHi 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
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)
and attached