# Thread: Formula to return the decimal of a Value

1. Registered User
Join Date
Feb 2004
Posts
533

## Unanswered: Formula to return the decimal of a Value

Is there another formula that will return just the fraction value of a decimal number. I came up with this:

=A1-INT(A1)

If A1 has 5.3 this would return .3
I've seen another way of doing this but don't remember what it is.

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

Your formula won't produce the correct result if A1 contains a negative number.

If negative numbers are not a concern then you can simplify it to:
Code:
`=MOD(A1,1)`
If negative numbers are a concern then you could use a variation such as:
Code:
`=ABS(MOD(A1,SIGN(A1)))`
Hope that helps...

3. Registered User
Join Date
Feb 2004
Posts
533
Colin,

This works great in a formula.
=MOD(A1, 1)

In VB this does not work due to the rounding of values by the Mod Operator. I was looking for some options that would work well in VB.

val = Range(A1) Mod 1

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result.

I'm pasting some values from a pivot table into another sheet and want to set the decimal to 1 place if it has a decimal or 0 decimals if it is a whole number. The original formula I posted is doing the trick. I was curious about optional ways of doing this.

Thanks for the suggestions.

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi Bill,

There are some differences between the VBA Mod operator and the Excel Mod worksheet function.

If you want to, you can indirectly call the Excel Mod worksheet function in VBA by evaluating the formula expression. For example:
Code:
`Debug.Print CStr(Sheet1.Evaluate("=MOD(A1,1)")`
Hope that helps...