[Solved] Condition if decimal .5

[jimbokea]

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

[RusselB]

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

[jimbokea]

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

[robleyd]

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.

[jimbokea]

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

[RusselB]

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

Code: Select all

IF(MOD(D4;1)=0)

in place of your first formula.

[jimbokea]

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