Solved

# Elusive third digit

Posted on 2013-11-01
273 Views
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
Question by:PipMic
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

Author Comment

ID: 39617326

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

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

LVL 43

Assisted Solution

Saqib Husain, Syed earned 167 total points
ID: 39617354
Or

=A1-ROUND(A1,2)
0

LVL 12

Assisted Solution

Harry Lee earned 83 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

ID: 39624560
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.