D
danlinksman
I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.
formula is not a whole number.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
E
Elkar
Use this formula in your Conditional Formatting:
=MOD(A1,1)<>0
Where cell A1 is the cell where your formula is located.
HTH,
Elkar
=MOD(A1,1)<>0
Where cell A1 is the cell where your formula is located.
HTH,
Elkar
D
danlinksman
It does not appear to be working correctly. IF cell M-2 has a formula of
L2/I2 and the results are not a whole number I want the cell to flag itself
with a color. If it comes our as a whole number the cell color stays normal.
When I tested your formula the conditional formatting continued did not work
properly. I was wanting the cell to turn "yellow" when, for instance, a
number came out 1.25 instead of 1.0. IF the cell result was 1.0 the cell
would look normal. Any more ideas?
L2/I2 and the results are not a whole number I want the cell to flag itself
with a color. If it comes our as a whole number the cell color stays normal.
When I tested your formula the conditional formatting continued did not work
properly. I was wanting the cell to turn "yellow" when, for instance, a
number came out 1.25 instead of 1.0. IF the cell result was 1.0 the cell
would look normal. Any more ideas?
E
Elkar
That formula should work for what you're describing. Let's make sure you've
got it set up properly. Follow these steps:
Select cell M2
From the Format Menu, choose "Conditional Formatting"
Change "Cell Value Is" to "Formula Is"
Enter the formula: =MOD(M2,1)<>0
Click the "Format" Button and select a yellow background
Click OK
Click OK
That should work.
got it set up properly. Follow these steps:
Select cell M2
From the Format Menu, choose "Conditional Formatting"
Change "Cell Value Is" to "Formula Is"
Enter the formula: =MOD(M2,1)<>0
Click the "Format" Button and select a yellow background
Click OK
Click OK
That should work.
D
danlinksman
I set it up just as you showed. If my formula resides in cell M2 and cell L2
= 1.83 and cell I2 = 2 and I divide L2/I2 I do not come out with a whole
number in cell M2. The formula you suggested turns the cell yellow, however
if cell L2 =2 and I2 = 2 I come out with a whole number of 1 and the cell
still remains yellow.
= 1.83 and cell I2 = 2 and I divide L2/I2 I do not come out with a whole
number in cell M2. The formula you suggested turns the cell yellow, however
if cell L2 =2 and I2 = 2 I come out with a whole number of 1 and the cell
still remains yellow.
E
Elkar
It works just fine for me. The only thing I can think of is that maybe
you've set the default background to yellow as well? Try chaning the cell
background to "No Fill" and then see what happens.
you've set the default background to yellow as well? Try chaning the cell
background to "No Fill" and then see what happens.
D
David Biddulph
Do you have calculation set to Automatic or Manual?
Are the L2 and I2 values the number 2 typed into the cell, or is at least
one of them the result of a formula which isn't exactly 2?
Are the L2 and I2 values the number 2 typed into the cell, or is at least
one of them the result of a formula which isn't exactly 2?
D
danlinksman
Try this in a spreadsheet. Colume A = 1.83 Colume B = 5279.878 Colume
C = 5278.028 Colume D = sum B-C . Now in colume E set your conditional
format to yellow and it appears to work correct. But if you change the value
of cell C to 5278.048 colume E cells result should turn back to white and it
does not. (At least on my system)??? On another idea presented by David he
felt it could be the result of the formula in cell D? If B - C = 1 I dont
think its a rounding issue unless Excel has some funky rounding issues.
C = 5278.028 Colume D = sum B-C . Now in colume E set your conditional
format to yellow and it appears to work correct. But if you change the value
of cell C to 5278.048 colume E cells result should turn back to white and it
does not. (At least on my system)??? On another idea presented by David he
felt it could be the result of the formula in cell D? If B - C = 1 I dont
think its a rounding issue unless Excel has some funky rounding issues.
E
Elkar
Ok, I see what you're talking about now. Excel does have some "rounding
issues" at times due to the way that numbers are stored. You can learn more
about that here if you're interested:
http://www.cpearson.com/excel/rounding.htm
To get around this, try adjusting your formula in Column D to:
=ROUND(B2-C2,9)
By placing the decimal precision out to 9 places, it should not have any
adverse effects on your data, but should fix the "rounding issue".
HTH,
Elkar
issues" at times due to the way that numbers are stored. You can learn more
about that here if you're interested:
http://www.cpearson.com/excel/rounding.htm
To get around this, try adjusting your formula in Column D to:
=ROUND(B2-C2,9)
By placing the decimal precision out to 9 places, it should not have any
adverse effects on your data, but should fix the "rounding issue".
HTH,
Elkar
D
danlinksman
Your rounding formula worked!!! Nice. Thank you so much for the help.
Have a great night.
Have a great night.