Conditional Formatting - on decimal place values?

[Adendum]

Is it possible to set up conditional formatting on cell values so that if the calculated value is not a whole number (e.g. 123.00) the formatting changes.

I am trying to highlight only those values that are whole numbers and want to 'hide' or even not show any values that are not whole numbers.

[FJCC]

You can set the conditional formatting to use a formula

Code: Select all

MOD(A1;1) = 0

This will flag cells that contain exact integers. If the cell contents are the results of a calculation, you will want to embed the ROUND function so that rounding problems don't cause errors. For example, limiting the precision to 4 decimal places:

Code: Select all

MOD(ROUND(A1;4);1) = 0

You can set the formatting so that cells that fail the test use a font color that matches the background, so they will appear empty.

[Charlie Young]

Is it possible to set up conditional formatting on cell values so that if the calculated value is not a whole number (e.g. 123.00) the formatting changes.

I am trying to highlight only those values that are whole numbers and want to 'hide' or even not show any values that are not whole numbers.

Just create a cell style, say "HideNumber," with the font color the same as the background color. In conditional formatting then, choose "Formula is" instead of "Cell value is," enter as the formula cell<>INT(cell) (where cell is the address of the cell you're formatting) and apply that style, and then also you can create some style for your whole numbers, and apply it with the formula cell=INT(cell). There would be some other niceties if you want to check if the cell contains a string and so forth. Note that the font color trick only hides the value in the cell; it still shows on the input line.

Edit: Too slow, but either way will work.