Apache OpenOffice Community Forum

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.

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>

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

Thank you both. Your suggestion worked a treat, acknak.