[Solved] condition if decimal .5

Discuss the spreadsheet application

[Solved] condition if decimal .5

Postby jimbokea » Sat May 11, 2019 12:17 am

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
Last edited by jimbokea on Sat May 11, 2019 6:53 am, edited 1 time in total.
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: condition if decimal .5

Postby RusselB » Sat May 11, 2019 5:31 am

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
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: condition if decimal .5

Postby jimbokea » Sat May 11, 2019 5:52 am

Hi - Can't see the relevance of MOD in this instance
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: condition if decimal .5

Postby robleyd » Sat May 11, 2019 6:17 am

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.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2885
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: condition if decimal .5

Postby jimbokea » Sat May 11, 2019 6:52 am

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
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am

Re: [Solved] condition if decimal .5

Postby RusselB » Sat May 11, 2019 1:46 pm

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   Expand viewCollapse view
IF(MOD(D4;1)=0)
in place of your first formula.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] condition if decimal .5

Postby jimbokea » Sun May 12, 2019 12:17 am

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
OPenOffice 4.1.1 on Windows 7
jimbokea
 
Posts: 171
Joined: Fri Jul 15, 2011 1:10 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests