[Solved] Change background colour of cells based on integer

[Zaphod Beebledoc]

I want to change the background colour of cells based on if they contain just integers. Can I do this?


Edit: I'm using Apache OpenOffice v4.1.3 on a Windows 10 Home machine, as there might be confusion between AOO and Libre...

[RusselB]

My first thought is that this could be done using Format -> Conditional formatting
However, checking the options, I don't see a way to specify that the number must be an integer.

[Zaphod Beebledoc]

That's where I looked first.

[Lupp]

Suppose we are talking of the value of cell A1. The standard way to test it for being integer is

Code: Select all

=(A1=INT(A1))

You may test with any different variant of rounding to an integer as there are (available as standard functions in Calc) ROUND, ROUNDDOWN, ROUNDUP, FLOOR, CEILING (Forgot one?). The "UP" and "DOWN" here are concerning the absolute amount. (Bad idea imo.)

There are many issues, however. Technical, mathematical, behavourial ...
You should ask simpler questions.

As long as you are working with tiny numbers up to an amount of about 2^49 (562949953421312), the conversion issue should not be significant. But there may occur very serious errors due to different issues, if the distinction between integer and not integer is critical. Calc cannot find mathematical facts about the property.

[Zaphod Beebledoc]

Found it! In the Conditional Formatting dialogue, you can select between All Cells, Cell Value is, Formula is, and Date is.

If you select Formula is and =Int(A1)=A1, then apply the formatting you want, in my case a green background, that will do the trick.

[Lupp]

Yes, of course. This will not make vanish the issues I talked of.
(I wrongly assumed you knew the 'Formula is' mode of CF. Sorry!)

[RusselB]

Found it! In the Conditional Formatting dialogue, you can select between All Cells, Cell Value is, Formula is, and Date is.

If you select Formula is and =Int(A1)=A1, then apply the formatting you want, in my case a green background, that will do the trick.

Interesting... my version of Apache OpenOffice 4.1.3 only has the Cell Value is and Formula is options.

[Zaphod Beebledoc]

Found it! In the Conditional Formatting dialogue, you can select between All Cells, Cell Value is, Formula is, and Date is.

If you select Formula is and =Int(A1)=A1, then apply the formatting you want, in my case a green background, that will do the trick.

Interesting... my version of Apache OpenOffice 4.1.3 only has the Cell Value is and Formula is options.

Ah yes, just realised I was using LibreOffice for this spreadsheet. I use both software suites. I need to update my signature as well... (updated)

[Zaphod Beebledoc]

(I wrongly assumed you knew the 'Formula is' mode of CF. Sorry!)

A RTFM error on my part...