[Solved] Use ROUNDUP and IF in conditional formatting

[Mettropolis]

Hi, so I'm a pretty inexperienced user who normally doesn't go deeper than sums for oppenoffice, who is trying to learn more for a specific application. Per attached image, I have a series of columns of three numbers. I want to maintain my ability to see exactly what each number is to two decimals, however I want to use conditional formatting to compare *rounded up* versions of lines 6 and 7, to a rounded up version of line 5, and if equal, maintain default color, if great than, pink, and if less than, green. I thought I had it figured out from googling, but something is not working.

You can see in the attached image, those two green cells are incorrect (only the two green cells have conditional formatting in that image, all else is manually colored). Line 6 should be grey (2.5 rounded up = 3 rounded up), and line 7 should be pink (3.5 rounded up is > 3 rounded up). I know *something* is happening with the formatting I've put in, because if I change line 5 to a 1, then the cells both turn pink.

Most of the examples I found googling were using IF statements in regular cells and formulas, it was hard to find examples of if statements in conditional formatting. Can what I'm attempting here be done with openoffice's tools?

[MrProgrammer]

I'm a pretty inexperienced user who normally doesn't go deeper than sums for oppenoffice,

[Tutorial] Ten concepts that every Calc user should know
Learning that material will save you hours of time and frustration, and may prevent data loss.

Per attached image …

An image is not helpful because it doesn't have enough information to show how the sheet's data and formatting are organized. Attach a document demonstrating the difficulty. I will not respond again without an ODS (spreadsheet) file attachment.

… using IF statements …

Calc has no statements. IF is a function. But use of the function is unnecessary in this situation. Use IF() only when you need to specify the values to be returned depending on the condition, like IF(condition;D5+1;D5-F6). The two expressions below are equivalent and return a TRUE/FALSE value:

   ROUNDUP(E6)=ROUNDUP(E5)
IF(ROUNDUP(E6)=ROUNDUP(E5))

Line 6 should be grey (2.5 rounded up = 3 rounded up), and line 7 should be pink (3.5 rounded up is > 3 rounded up).

If you want to make all the comparisons to the same cell, E5, you will need to understand absolute versus relative addressing in spreadsheets, covered in Section 8 of the link above. It will be difficult for you to do anything with formulas until you understand this concept. In a conditional formatting formula, references which are not absolute are relative to the cell which was active when you initiated the dialog.

[John_Ha]

Calc Functions listed by category is very useful as it has examples of each function's use.

[Mettropolis]

Ok so it turned out to either be a background color mistake on my part, or something about removing the if and changing the parenthesis did it. Sorry for wasting folks' time

[keme]

No need to apologise.

The application of cell border/background by conditional formatting should override the applied "base style" and manual formatting for the cell, but this behavior is sometimes a little erratic. I have not tested it thoroughly in recent versions, so it may be more reliable if you upgrade.

The comments about absolute vs. relative references are relevant if you apply conditional formatting to a range of cells, or copy the formatting. Make sure to look into that if it applies to your workflow.

The root cause of your issue was that your conditions compared a boolean to a numeric value. The second closing parenthesis closes the IF() without including the THEN and ELSE parts, so the IF function simply evaluates the rounded value as a boolean, and your formula then proceeded to compare that to the second rounded value. Your editing of the parentheses rectified this.

In other words: IF(ROUNDUP(x)) will return FALSE (which translates to numerical zero) if x is between -1 and 0, and TRUE (which is 1) otherwise. You are then comparing this to an actual number, which does not make sense.

Code: Select all

IF(ROUNDUP(x))<(ROUNDUP(y))  - this is nonsense
IF(ROUNDUP(x)<ROUNDUP(y))    - makes sense, and should do what you want
ROUNDUP(x)<ROUNDUP(y)        - also makes sense, and should do exactly the same as the IF() above.