Post by neil-uk » Wed Apr 01, 2020 1:21 pm
I have a quick question on what I think is Conditional Formatting.
If I have a data set in a column of various numbers. These numbers vary depending on calculations.
I'm looking to simply highlight the highest number, ideally changing it's 'look' to Bold and Green.
Could anyone help me with a suitable Conditional Formatting formula?
Last edited by Hagar Delest on Fri Apr 17, 2020 9:37 pm, edited 1 time in total.
Reason: tagged solved
Post by RusselB » Wed Apr 01, 2020 2:20 pm
In the Conditional Formatting dialog, change the first dropdown to read Formula is
Then in the box to the right, enter the appropriate formula using MAX for the highest or MIN for the lowest. Start with the HELP file if you need more help with these functions
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post by Villeroy » Wed Apr 01, 2020 4:57 pm
1. You need cell styles.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
Post by neil-uk » Thu Apr 16, 2020 7:49 pm
Sorry, I give up on this!
So, I have two columns of Data
O1:O10 with percentages
P1:P10 with random text.
I want to run the Max formula to show me the highest percentage - and for it to turn the cell green with a white text. Min should be red cell with with white text.
So if O1 is 3% and O7 is 10%, O7 goes green, O1 is red.
Cell Styles are fine with me. All set up.
I thought the formula it would be MAX(O1:O10)"? But it's not working.
Sorry to ask, but HELP!
NeoOffice 2.2.3 with MacOS 10.4 Villeroy Volunteer Posts: 31308 Joined: Mon Oct 08, 2007 1:35 am Location: GermanyPost by Villeroy » Thu Apr 16, 2020 8:21 pm
"Cell Value Is" MAX($O$1:$O$10)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
Post by neil-uk » Fri Apr 17, 2020 11:35 am
Thanks for this!
It worked in my O Column, but the text in P remains black.
Perhaps I didn't explain I wanted the O and P to do the same thing, based on the Data in O. So whatever row in O would have the highest number, it would turn the relevant row in both O and P with that cell style.
Would there be a formula difference to do this? It's working great on Column O!
Sorry, I should have explained better!
NeoOffice 2.2.3 with MacOS 10.4 Villeroy Volunteer Posts: 31308 Joined: Mon Oct 08, 2007 1:35 am Location: GermanyPost by Villeroy » Fri Apr 17, 2020 11:44 am
That's what the second formula can do if you select a 2-dimensional range. The "1" in $O1 depends on the row number of the active cell when calling the OpenOffice dialog. I think, NeoOffice has the same dialog. When using LibreOffice, The "1" in $O1 should be the number of the first selected row regardless of the current cell cursor position.
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
Post by neil-uk » Fri Apr 17, 2020 11:52 am
Hmm strangely not.
Uploaded a Test File. Can't quite see what I'm doing wrong?
Attachments MinMax Test.ods (13.89 KiB) Downloaded 182 times NeoOffice 2.2.3 with MacOS 10.4 Villeroy Volunteer Posts: 31308 Joined: Mon Oct 08, 2007 1:35 am Location: GermanyPost by Villeroy » Fri Apr 17, 2020 4:02 pm
You applied the formula while the cell cursor was in the last row. Rows 1 to 9 show $A#REF!=MAX($A$1:$A$10) because there is no relative row above the first one.
If the cell cursor (the active cell) is in row 10, the correct row formula would be
$A10=MAX($A$1:$A$10) meaning the cell in column $A of this row equals the max of absolute range $A$1:$A$10. "This row" is the row having the cell cursor and $A means just absolute column A and $A$1:$A$10 means these 10 cells absolutely.
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
Post by neil-uk » Fri Apr 17, 2020 6:01 pm
That is perfect, thank you!
It was where the cursor goes that must have thrown things out!
NeoOffice 2.2.3 with MacOS 10.4 Villeroy Volunteer Posts: 31308 Joined: Mon Oct 08, 2007 1:35 am Location: GermanyPost by Villeroy » Fri Apr 17, 2020 6:42 pm
In order to reproduce this error with a sheet formula:
Enter =C3 into cell F10 of a blank sheet.
Copy F10 upwards and to the left.
Watch the resulting formulas.
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
Post by robleyd » Sat Apr 18, 2020 2:25 am
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.8.0.3; SlackBuild for 24.8.0 by Eric Hameleers
---------------------
Roses are Red, Violets are Blue Unexpected '
12 posts • Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited