Conditional Formatting on Whole Numbers?
 

I have a spreadsheet that I want to highlight cell values that are whole
or real numbers - not decimals. I am having a heck of a time trying to
figure out the best way to do this. :confused: At this point, I really
don't care if they are highlighted or what, as long as the whole numbers
stand out somehow. The cells themselves contain a formula (i.e., B$1/A2)
So, if this formula results in a whole number (i.e., 1, 2, 3, 4 etc...)
not a decimal (i.e., 1.1, 1.2, 1.3, etc...) then I want it to stand
out.

I even tried nesting an "IF" function so that anything containing a
decimal would show the word "NO" instead of the value, but I couldn't
get it to work. Here's the formula I was trying, maybe someone can tell
me what I did wrong with it. :mad:

=IF(PRODUCT(B$1/A2),"*.*","NO",PRODUCT(B$1/A2))
I get an error that I'm using too many arguements.

Please help! Thank you in advance.
ExcelGuruDummy


--
excelgurudummy
------------------------------------------------------------------------
excelgurudummy's Profile: http://www.excelforum.com/member.php...o&userid=25441
View this thread: http://www.excelforum.com/showthread...hreadid=388903

Hi!


Conditional Formatting
Formula is: A1-INT(A1)=0

If the values will always be greater than or equal to 1, you could use:

=MOD(A1,1)=0

Biff

"excelgurudummy"
<excelgurudummy.1shqid_1121900760.7873@excelforu m-nospam.com wrote in
message news:excelgurudummy.1shqid_1121900760.7873@excelfo rum-nospam.com...

I have a spreadsheet that I want to highlight cell values that are whole
or real numbers - not decimals. I am having a heck of a time trying to
figure out the best way to do this. :confused: At this point, I really
don't care if they are highlighted or what, as long as the whole numbers
stand out somehow. The cells themselves contain a formula (i.e., B$1/A2)
So, if this formula results in a whole number (i.e., 1, 2, 3, 4 etc...)
not a decimal (i.e., 1.1, 1.2, 1.3, etc...) then I want it to stand
out.

I even tried nesting an "IF" function so that anything containing a
decimal would show the word "NO" instead of the value, but I couldn't
get it to work. Here's the formula I was trying, maybe someone can tell
me what I did wrong with it. :mad:

=IF(PRODUCT(B$1/A2),"*.*","NO",PRODUCT(B$1/A2))
I get an error that I'm using too many arguements.

Please help! Thank you in advance.
ExcelGuruDummy


--
excelgurudummy
------------------------------------------------------------------------
excelgurudummy's Profile:
http://www.excelforum.com/member.php...o&userid=25441
View this thread: http://www.excelforum.com/showthread...hreadid=388903

Hi,

Lets say the first cell in your range is A1, hightlight the entire
range, then go to Format-conditional formatting and choose 'cell value'
and choose 'greater than' and enter this formula

=INT(A1)

and choose the format you need.

INT(A1) roundsdown the value of the cell to a whole number. If there is
a decimal, then the value in the cell would be greater than the Integer
value of the cell and hence this formula will highlight the numbers with
decimals.

Govind.

excelgurudummy wrote:
I have a spreadsheet that I want to highlight cell values that are whole
or real numbers - not decimals. I am having a heck of a time trying to
figure out the best way to do this. :confused: At this point, I really
don't care if they are highlighted or what, as long as the whole numbers
stand out somehow. The cells themselves contain a formula (i.e., B$1/A2)
So, if this formula results in a whole number (i.e., 1, 2, 3, 4 etc...)
not a decimal (i.e., 1.1, 1.2, 1.3, etc...) then I want it to stand
out.

I even tried nesting an "IF" function so that anything containing a
decimal would show the word "NO" instead of the value, but I couldn't
get it to work. Here's the formula I was trying, maybe someone can tell
me what I did wrong with it. :mad:

=IF(PRODUCT(B$1/A2),"*.*","NO",PRODUCT(B$1/A2))
I get an error that I'm using too many arguements.

Please help! Thank you in advance.
ExcelGuruDummy

try using the TRUNC function in your conditional formatting :

conditional formatting set to "Formula is" - enter

[COLOR=Blue=IF(TRUNC(A1)-A1=0,TRUE,FALSE)[/color] in the condition.

This returns FALSE if G7 is not an integer.

(not sure about your reference to Real Numbers (i.e. as opposed to
imaginary numbers))

Could also use INT in the same way if you prefer


--
optionbase1
------------------------------------------------------------------------
optionbase1's Profile: http://www.excelforum.com/member.php...o&userid=25212
View this thread: http://www.excelforum.com/showthread...hreadid=388903

Hi,

Sorry. since you want to highlight the whole numbers, you should choose

'cell value' and choose 'equal to' and enter this formula

=INT(A1)
in conditional formatting.

Govind.

Govind wrote:

Hi,

Lets say the first cell in your range is A1, hightlight the entire
range, then go to Format-conditional formatting and choose 'cell value'
and choose 'greater than' and enter this formula

=INT(A1)

and choose the format you need.

INT(A1) roundsdown the value of the cell to a whole number. If there is
a decimal, then the value in the cell would be greater than the Integer
value of the cell and hence this formula will highlight the numbers with
decimals.

Govind.

excelgurudummy wrote:

I have a spreadsheet that I want to highlight cell values that are whole
or real numbers - not decimals. I am having a heck of a time trying to
figure out the best way to do this. :confused: At this point, I really
don't care if they are highlighted or what, as long as the whole numbers
stand out somehow. The cells themselves contain a formula (i.e., B$1/A2)
So, if this formula results in a whole number (i.e., 1, 2, 3, 4 etc...)
not a decimal (i.e., 1.1, 1.2, 1.3, etc...) then I want it to stand
out.

I even tried nesting an "IF" function so that anything containing a
decimal would show the word "NO" instead of the value, but I couldn't
get it to work. Here's the formula I was trying, maybe someone can tell
me what I did wrong with it. :mad:
=IF(PRODUCT(B$1/A2),"*.*","NO",PRODUCT(B$1/A2))
I get an error that I'm using too many arguements.
Please help! Thank you in advance.
ExcelGuruDummy

Thank you SO much! This is awesome. You have no idea how long I've been
trying to make this work. I did not realize you could put a formula in
conditional formatting (duh!) You guys are all so smart!!

Thanks again!


--
excelgurudummy
------------------------------------------------------------------------
excelgurudummy's Profile: http://www.excelforum.com/member.php...o&userid=25441
View this thread: http://www.excelforum.com/showthread...hreadid=388903

Hi,

Thanks for the feedback.

Govind.

excelgurudummy wrote:

Thank you SO much! This is awesome. You have no idea how long I've been
trying to make this work. I did not realize you could put a formula in
conditional formatting (duh!) You guys are all so smart!!

Thanks again!