Elusive third digit

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
PipMicAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
=A1-INT(A1*100)/100
0
 
PipMicAuthor Commented:
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
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Or

=A1-ROUND(A1,2)
0
 
Harry LeeConnect With a Mentor Commented:
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
 
PipMicAuthor Commented:
excellent advice
0
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.

All Courses

From novice to tech pro — start learning today.