Conditional Formatting on Whole Numbers?

[excelgurudummy]

I have a spreadsheet that I want to highlight cell values that are whol
or real numbers - not decimals. I am having a heck of a time trying t
figure out the best way to do this. At this point, I reall
don't care if they are highlighted or what, as long as the whole number
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 stan
out.

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

=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.
ExcelGuruDumm

 

[Biff]

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"

 

[Govind]

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.

 

[optionbase1]

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)[/QUOTE] 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

 

[Govind]

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.

 

[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!

 

[Govind]

Hi,

Thanks for the feedback.

Govind.