whole number conditional formatting
 

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

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.

"Elkar" wrote:

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

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.

"danlinksman" wrote:

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.

"Elkar" wrote:

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

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?
--
David Biddulph

"danlinksman" wrote in message
...
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.

"Elkar" wrote:

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a
particular
formula is not a whole number.

whole number conditional formatting
 

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.

"Elkar" wrote:

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.

"danlinksman" wrote:

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.

"Elkar" wrote:

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

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



"danlinksman" wrote:

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.

"Elkar" wrote:

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.

"danlinksman" wrote:

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.

"Elkar" wrote:

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a particular
formula is not a whole number.

whole number conditional formatting
 

Your rounding formula worked!!! Nice. Thank you so much for the help.

Have a great night.

"David Biddulph" wrote:

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?
--
David Biddulph

"danlinksman" wrote in message
...
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.

"Elkar" wrote:

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.


"danlinksman" wrote:

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?

"Elkar" wrote:

Use this formula in your Conditional Formatting:

=MOD(A1,1)<0

Where cell A1 is the cell where your formula is located.

HTH,
Elkar


"danlinksman" wrote:

I would like the cell to turn a highlight color if the result of a
particular
formula is not a whole number.