Elusive third digit

Posted on 2013-11-01
Medium Priority
Last Modified: 2013-11-05
Hi all,

Grateful for assistance.

I have a long column of amounts (currency 2 dec) in an Excel worksheet. I have accidentally entered three digits after the decimal point for a particular amount .

I would like to run a formula in a side column which can identify the elusive amount with the third digit.

Question by:PipMic
  • 2
  • 2

Author Comment

ID: 39617326
I had thought:

 of multiplying the amount by 100, then converting the result to a value and then checking the last digit to see if it is greater than zero thereby identifying that cell with the elusive amount.
LVL 43

Accepted Solution

Saqib Husain, Syed earned 668 total points
ID: 39617329
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 668 total points
ID: 39617354

LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 332 total points
ID: 39618003
If all you want is to identify them instead of fixing them, you can use formula like
=if(a1-round(A1,2)=0,"","3 Dec Places"

If you want to fix it, you can simply do =round(a1,2), =roundup(a1,2), or =rounddown(a1,2), and copy the formula down the list. Copy the formulated column and paste back to the original column using Paste Special Value.

Author Closing Comment

ID: 39624560
excellent advice

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question