Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Elusive third digit

Posted on 2013-11-01
5
Medium Priority
?
291 Views
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.

Thanks
0
Comment
Question by:PipMic
  • 2
  • 2
5 Comments
 

Author Comment

by:PipMic
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.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 668 total points
ID: 39617329
=A1-INT(A1*100)/100
0
 
LVL 43

Assisted Solution

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

=A1-ROUND(A1,2)
0
 
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.
0
 

Author Closing Comment

by:PipMic
ID: 39624560
excellent advice
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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