Formula to identify decimals??
 

Thanks in advance for any help you all provide!!

I am trying to come up with a function that will identify if a cell has
a decimal in it. My staff will be entering budgetary amounts into a
spreadsheet and I want the amounts to be in whole dollars. I want to
be able to use this function with conditional formatting to identify
the decimal (error) when they enter it.

does that make sense?


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=513350

Formula to identify decimals??
 

For your Conditional Formatting formula use this:

=MOD(A1,1)<0

You could also use Data Validation to prevent entry of these numbers to
begin with. This would allow you to customize an error message so the user
would know why the value is invalid. Using this formula:

=MOD(A1,1)=0

HTH,
Elkar

"Celt" wrote:


Thanks in advance for any help you all provide!!

I am trying to come up with a function that will identify if a cell has
a decimal in it. My staff will be entering budgetary amounts into a
spreadsheet and I want the amounts to be in whole dollars. I want to
be able to use this function with conditional formatting to identify
the decimal (error) when they enter it.

does that make sense?


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=513350

Formula to identify decimals??
 

Assuming you want to check column A, try this formula somewhere else:

=IF(A1=INT(A1),"no decimals","decimals")

and copy down. Of course, you could change the first message to ""
(i.e. blank) so that the other message stands out as you scroll down
the page. If you don't expect many of these you can apply a filter to
the column with the formula in to just display the cells with decimals.
With conditional formatting you could change the foreground or
background colour (or both) for further emphasis.

Hope this helps.

Pete

Formula to identify decimals??
 

Worked great!!! Thanks!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=513350

Formula to identify decimals??
 

That was a few days ago, but thanks for the feedback.

Pete