Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 23 May 2018 15:32:13 +0200Can't highlight MAX value in a row, MIN value works...what am I doing wrong??https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/ Hello,
I am trying to use conditional formatting to highlight the biggest and smallest values in a column. I have setup my conditional formatting to look like the following:
![image description](/upfiles/1526922943171802.png)
I want to highlight the biggest number in Green and the smallest number in Red. No matter what I seem to do the MAX formula conditional format never works. The smallest value gets highlighted in red, but I never see anything in Green.
Do I need to do something different when using two conditional formats in the same column? This seems like it should be a simple process but I have really been struggling.
Any help would be greatly appreciated. I'm also attaching the first row of my spreadsheet so someone can see what I'm trying to do.
Thank you,
-Chad
[C:\fakepath\May 2018 Test sample.xlsx](/upfiles/15269236124748795.xlsx)Mon, 21 May 2018 19:29:47 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/Comment by Grantler for <p>Hello,</p>
<p>I am trying to use conditional formatting to highlight the biggest and smallest values in a column. I have setup my conditional formatting to look like the following:</p>
<p><img alt="image description" src="/upfiles/1526922943171802.png"></p>
<p>I want to highlight the biggest number in Green and the smallest number in Red. No matter what I seem to do the MAX formula conditional format never works. The smallest value gets highlighted in red, but I never see anything in Green.</p>
<p>Do I need to do something different when using two conditional formats in the same column? This seems like it should be a simple process but I have really been struggling. </p>
<p>Any help would be greatly appreciated. I'm also attaching the first row of my spreadsheet so someone can see what I'm trying to do. </p>
<p>Thank you,
-Chad</p>
<p><a href="/upfiles/15269236124748795.xlsx">C:\fakepath\May 2018 Test sample.xlsx</a></p>
https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155622#post-id-155622Working on an XLSX file with LO is a dubious idea. Nevertheless you should use absolute addresses for your Conditional Formatting.
Example:
MAX($D$5:$D$35)
After changing the formula worked as it should do. Either as an ODS or as an XLSX file. (LO 6.0.4.2 on LinuxMint)Mon, 21 May 2018 19:59:33 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155622#post-id-155622Answer by Regina for <p>Hello,</p>
<p>I am trying to use conditional formatting to highlight the biggest and smallest values in a column. I have setup my conditional formatting to look like the following:</p>
<p><img alt="image description" src="/upfiles/1526922943171802.png"></p>
<p>I want to highlight the biggest number in Green and the smallest number in Red. No matter what I seem to do the MAX formula conditional format never works. The smallest value gets highlighted in red, but I never see anything in Green.</p>
<p>Do I need to do something different when using two conditional formats in the same column? This seems like it should be a simple process but I have really been struggling. </p>
<p>Any help would be greatly appreciated. I'm also attaching the first row of my spreadsheet so someone can see what I'm trying to do. </p>
<p>Thank you,
-Chad</p>
<p><a href="/upfiles/15269236124748795.xlsx">C:\fakepath\May 2018 Test sample.xlsx</a></p>
https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?answer=155631#post-id-155631The formula in the condition has a Basis-cell, in your case D5. That means, that the formula is written as if it is used for cell D5. So for D5 the condition is evaluated as
if ( D5 equal to MAX(D5:D35) )
apply ConditionalStyle_1
else if (D5 equal to MIN(D5:D35)
apply ConditionalStyle_2
The cell e.g. D14 is nine rows down, so the entire comparison is shifted down and the evaluation is now
if ( D14 equal to MAX(D14:D44) )
apply ConditionalStyle_1
else if (D14 equal to MIN(D14:D44)
apply ConditionalStyle_2
So if you want, that the range keeps the same for all comparisons, then you need absolute addressing in the range.
Try is with `Cell value is equal to MAX(D$5:D$35)`.
But the question remains, why there is not any green. With the formula MAX(D5:D35) there should be green in 825.71 , 785.82 , 758.09 , 720.34 , 710.85 and 483.00. I don't know the reason. If you copy the range and paste it to a different place in the sheet, you get the green items. A hard recalculate does not force the green items in the original range D5:D24.
Edit: I found the reason. Below the orange range, there is the total in D36. So because of the shifting from D6 downwards, the range, which is used for comparison always contains the total. Therefore no cell of the orange range can be the maximum.Mon, 21 May 2018 20:28:19 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?answer=155631#post-id-155631Answer by kompilainenn for <p>Hello,</p>
<p>I am trying to use conditional formatting to highlight the biggest and smallest values in a column. I have setup my conditional formatting to look like the following:</p>
<p><img alt="image description" src="/upfiles/1526922943171802.png"></p>
<p>I want to highlight the biggest number in Green and the smallest number in Red. No matter what I seem to do the MAX formula conditional format never works. The smallest value gets highlighted in red, but I never see anything in Green.</p>
<p>Do I need to do something different when using two conditional formats in the same column? This seems like it should be a simple process but I have really been struggling. </p>
<p>Any help would be greatly appreciated. I'm also attaching the first row of my spreadsheet so someone can see what I'm trying to do. </p>
<p>Thank you,
-Chad</p>
<p><a href="/upfiles/15269236124748795.xlsx">C:\fakepath\May 2018 Test sample.xlsx</a></p>
https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?answer=155635#post-id-155635You may use:
for first Condition : Cell value is - top 10 elements - 1
for second condition: Cell value is - bottom 10 elements - 1
http://i.imgur.com/zMfCHDN.png
ps: number 10 is a mistake in GUI. Need enter value in to right fieldMon, 21 May 2018 20:45:26 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?answer=155635#post-id-155635Comment by Mike Kaganski for <p>You may use:</p>
<p>for first Condition : Cell value is - top 10 elements - 1</p>
<p>for second condition: Cell value is - bottom 10 elements - 1</p>
<p><a href="http://i.imgur.com/zMfCHDN.png">http://i.imgur.com/zMfCHDN.png</a></p>
<p>ps: number 10 is a mistake in GUI. Need enter value in to right field</p>
https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155663#post-id-155663This is a better alternative to say "min/max value in the given range" instead of hardcoding the range in the formula.Tue, 22 May 2018 07:53:48 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155663#post-id-155663Comment by jackichad for <p>You may use:</p>
<p>for first Condition : Cell value is - top 10 elements - 1</p>
<p>for second condition: Cell value is - bottom 10 elements - 1</p>
<p><a href="http://i.imgur.com/zMfCHDN.png">http://i.imgur.com/zMfCHDN.png</a></p>
<p>ps: number 10 is a mistake in GUI. Need enter value in to right field</p>
https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155730#post-id-155730This is a great start! Just two quick questions:
1. Once I add the formatting to my range, I get my green(high) and red(low) but it also marks all empty/blank cells red, which makes sense since they are all a small value. Is there a way I can make it ignore blank cells until I input a value into it?
2. Once I get this working, will it be simple to copy the formatting to other columns?
Thank you very much!!
https://i.imgur.com/wsxnFbm.pngTue, 22 May 2018 21:19:29 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155730#post-id-155730Comment by kompilainenn for <p>You may use:</p>
<p>for first Condition : Cell value is - top 10 elements - 1</p>
<p>for second condition: Cell value is - bottom 10 elements - 1</p>
<p><a href="http://i.imgur.com/zMfCHDN.png">http://i.imgur.com/zMfCHDN.png</a></p>
<p>ps: number 10 is a mistake in GUI. Need enter value in to right field</p>
https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155799#post-id-1557991. There is a way: create else one condition for your range (push Add) with Cell value is - equal - "" with style Default, and move it to top of list (push button Up)
2. Yes, you can set a new size of rangeWed, 23 May 2018 15:32:13 +0200https://ask.libreoffice.org/en/question/155616/cant-highlight-max-value-in-a-row-min-value-workswhat-am-i-doing-wrong/?comment=155799#post-id-155799