Apache OpenOffice Community Forum

Hi
Is there a way to set in conditional formatting that if the cell has a decimal figure like say 5.5 to display a certain colour.
I do not want to use validity as that is already set for this cell to be a decimal or whole number.
I have set this cell to display figures in red if it is greater than the cell next to it but I want to sort of cancel that out if it is a decimal.
Cheers Jim

I admit I haven't used Conditional Formatting much, but a quick look suggest you could do this using the MOD function in the Conditional Formatting parameter

Hi - Can't see the relevance of MOD in this instance

MOD returns the remainder of a division operation; so for example dividing 5.5 by 1 would give a remainder of .5, but dividing an integer (whole number) by one would result in a remainder of zero.

See the HELP - F1 - for MOD for syntax.

I think I have solved this
All this was to avoid 2 sets of numbers being reversed when entered - had happened several times in the past
The second one was always equal or higher than the first and never a decimal
The first could be a decimal or a whole number

I used the MID function to see if the middle character is a "." in the first figure as in -
IF(MID(D4;2;1)=".")
This was set is a the first condition and the figures stayed normal colour if met

2nd condition - IF(E4="";"";IF(D4>E4)) turned the first figure red if higher than the second but not if the first figure was a decimal

seems to work

Your formula would fail with any number that is greater than 10 in D4, since 10.5 (for example) has the . in the 3rd location.
Also please note that the MID function is meant to work with text, not numbers. The fact that is does work, I think, is a fluke.
Since you seem to have a solution that works with a string function, you might try using the SEARCH or FIND function to search for the . in D4, rather than the MID function, as then the . would be detected no matter where in the number it is located.
Please note that, again, this uses a string function on a number, not something I would recommend.

Seeing the formula you did come up with, you could also try in place of your first formula.

Yes it would fail if over 10 but 10.5 would be rare as most ends max are 9. But it works with Right as well as in =IF(RIGHT(d4;2)=".5"; "ok";"") produces ok if d4 is 10.5.
You never know what happens until you try. It did not work till I put the " " around the .5 ( or .) so I guess it is accepting the .5 as text.
The fact that a decimal .5 is in that column means they have entered the figures the right way round as the next column should never have a .5 in it. I catered for this next column with validity check of not equal to a whole number. cheers Jim