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

Discuss the spreadsheet application
Post Reply
Recycler
Posts: 14
Joined: Sat Jan 05, 2008 8:15 pm

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

Post 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.
Last edited by Recycler on Sat Jan 19, 2008 8:15 pm, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MAXA conditional formatting only works on first instance?

Post 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>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: MAXA conditional formatting only works on first instance?

Post 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
AOO4/LO5 • Linux • Fedora 23
Recycler
Posts: 14
Joined: Sat Jan 05, 2008 8:15 pm

Re: MAXA conditional formatting only works on first instance?

Post by Recycler »

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