Page 1 of 1

[Solved] MAXA conditional formatting ...........

PostPosted: Sat Jan 19, 2008 10:45 am
by Recycler
MAXA Conditional formatting only works on first instance?

I have a column of calculated values. Using the MAXA function I have created a conditional formatting to highlight the maximum value cell with a red background colour (as Condition 1).

I observe though that if there are two instances where the cell value equals the highest value, only the one higher up the column is apparently picked out by the conditional highlighting.

In my specific case, the column runs from $I$14:$I$44, with displayed values 11.7,14.1,13.2,12.5, 14.1, 15.6, 15.6, 11.7,11, 13, 10.2, 9.9, 15.2, 8.8, 11.3, 12.6,12.5, 11.6, with the remaining entries presently empty. The Cell formatting is Number, #.#.

The Conditional Formatting test for this is "greater than or equal to" MAXA($I$14:$I$44). The underlying source values determining the additive calculated results only have one decimal place, so both the cells showing 15.6 display 15.600 if you increase the display 'resolution'.

I should say also that I have a second conditional formatting in place (as condition 2); this highlights any cells above an externally calculated reference value in blue. Both instances of 15.6 also fulfil this condition, but the upper one displays red and the lower one shows blue.

Is this expected behaviour, please? I would like any cells equalling the maximum to show red, not just the first found.

Many thanks.

Re: MAXA conditional formatting only works on first instance?

PostPosted: Sat Jan 19, 2008 10:54 am
by Villeroy
observe though that if there are two instances where the cell value equals the highest value, only the one higher up the column is apparently picked out by the conditional highlighting.

=cell1=cell2 :?:
=MAXA(range)=cell1 :?:
=MAXA(range)=cell2 :?:

If you encounter difficulties with conditional formatting, put your conditions into some range to see the result.

Edit:
How I would do what you obviously try to do:
Select $A$1:$A1000 (the range of values to compare)
Format>Conditional formatting...
[X]Condition 1
Cell value is
equal to
MAXA($A$1:$A1000)
Cell Style: <which ever>

Re: MAXA conditional formatting only works on first instance?

PostPosted: Sat Jan 19, 2008 7:12 pm
by acknak
Entering your data, and your condition, it works perfectly for me: both 15.6 values are highlighted.

There can be a big problem if your values are computed: you may have two values that display as "15.6" but they may not have the same value. Testing for equality will absolutely require that they have exactly the same value.

You could instead use a condition something like: Cell value is | Greater than or equal to | MAXA($I$14:$I$44)*0.999

Re: MAXA conditional formatting only works on first instance?

PostPosted: Sat Jan 19, 2008 8:14 pm
by Recycler
Thank you both. Your suggestion worked a treat, acknak.