I am looking for a way to take a mixed number (whole number with decimal), strip the whole number(s), leaving only the 2 digit decimal. The whole number portion can be 1 or more digits.
Example..........start with 10.46............I want to be left with .46
Example..........start with 128.32............I want to be left with .32
Is there a a function or operator that will remove any and all whole numbers?
Either this:
="."&RIGHT(A1,2)
or this:
=VALUE("."&RIGHT(A1,2))
The latter will keep the result as a number rather than text.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
=A1-INT(A1)
A1 = Your original number (10.46)
Try
=MOD(A1,1)
Actually I forgot to take negative integer into consideration. Try this formula instead.
=A1-ROUNDDOWN(A1,0)
Originally Posted by JieJenn
Or simply =A1-TRUNC(A1)
.... Which is equivalent to =A1-INT(A1). Note JieJenn's "aha!" moment about negative numbers. Good catch!
With all of these formulas, the problem is: if the formula is in A2, =A2=0.46 returns FALSE(!). You can see why by formatting A2 as Number with 16 decimal places (in case the fractional part is less than 0.10).
I could explain the why reasonably succinctly, but in some detail; just ask. The shortest answer is: it has to do with the anomalies of 64-bit binary floating-point, which is how Excel represents numbers internally.
More reliable:
=ROUND(A1-TRUNC(A1),2)
thank you all so much for the assistance
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks