# Formula to identify decimals??

1. ## Formula to identify decimals??

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?

2. ## RE: 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:

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

3. ## Re: 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

4. ## Right on...

Worked great!!! Thanks!

5. ## Re: Formula to identify decimals??

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

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1