Can someone help?

I need to know if there exists an excel function for use to return decimals
only in any given numer.

Example:

1241,17 to be displayed/returned as 17

18,875 to be displayed/returned as 875 etc.



Thanks for any help on this issue.

Rexburgh,

The MOD function returns the remainder from a division, you can get
the decimal portion of a number by dividing by 1... if your value was
in A1:

=MOD(A1,1)

Dan E

"rexburgh" wrote in message ...
Can someone help?

I need to know if there exists an excel function for use to return decimals
only in any given numer.

Example:

1241,17 to be displayed/returned as 17

18,875 to be displayed/returned as 875 etc.



Thanks for any help on this issue.

I should have mentioned that in VB it's used like any other arithmetic
operator ie.

NewVal = OldVal Mod 1
X = 10.325 Mod 1 - Returns 0.325

Dan E

"Dan E" wrote in message ...
Rexburgh,

The MOD function returns the remainder from a division, you can get
the decimal portion of a number by dividing by 1... if your value was
in A1:

=MOD(A1,1)

Dan E

"rexburgh" wrote in message ...
Can someone help?

I need to know if there exists an excel function for use to return decimals
only in any given numer.

Example:

1241,17 to be displayed/returned as 17

18,875 to be displayed/returned as 875 etc.



Thanks for any help on this issue.

the MOD worksheetfunction works quite differently from MOD operator in
VBA as all numbers are ROUNDED to INTEGERS BEFORE THE MOD is APPLIED


just run this macro and see why i'm carfull using VBA operators.
they work well... just completely different.


You'll need the 3rd construct to get the same results as the MOD
worksheetfunction.

Private Sub Workbook_Open()
Dim n
For Each n In Array(325, 324.9, -324.9)
Debug.Print n, n Mod 10
Next
For Each n In Array(325, 324.9, -324.9)
Debug.Print n, n - 10 * n \ 10
Next
For Each n In Array(325, 324.9, -324.9)
Debug.Print n, n - 10 * Int(n / 10)
Next
For Each n In Array(325, 324.9, -324.9)
Debug.Print n, Evaluate("=MOD(" & n & ",10)")
Next
End Sub

HTH

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dan E" wrote:

I should have mentioned that in VB it's used like any other arithmetic
operator ie.

NewVal = OldVal Mod 1
X = 10.325 Mod 1 - Returns 0.325

Dan E

"Dan E" wrote in message
...
Rexburgh,

The MOD function returns the remainder from a division, you can get
the decimal portion of a number by dividing by 1... if your value was
in A1:

=MOD(A1,1)

Dan E

"rexburgh" wrote in message
...
Can someone help?

I need to know if there exists an excel function for use to return
decimals only in any given numer.

Example:

1241,17 to be displayed/returned as 17

18,875 to be displayed/returned as 875 etc.



Thanks for any help on this issue.

Just banged that little demo in the first code window i had open..
and forgot to change the proc name :)

so disregard the work_book() and read it as ModDemo() or s'thing.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

keepitcool,

Thanks for the correction, I guess I should have tested that. My
apologies to all.

Dan E

"keepitcool" wrote in message ...
Just banged that little demo in the first code window i had open..
and forgot to change the proc name :)

so disregard the work_book() and read it as ModDemo() or s'thing.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

The mod vb/vba operator does not operate like the mod function in excel
(which is not available in VBA). It does not return a decimal:

Testing from the immediate window using your example.

? 10.325 Mod 1
0


In VB, you would truncate the number and subtract it from the original

? v
10.325
? v-int(v)
0.324999999999999

of course you see you are confronted by floating point storage issues and
you would need to build in a consideration for negative numbers.

--
Regards,
Tom Ogilvy


"Dan E" wrote in message
...
I should have mentioned that in VB it's used like any other arithmetic
operator ie.

NewVal = OldVal Mod 1
X = 10.325 Mod 1 - Returns 0.325

Dan E

"Dan E" wrote in message
...
Rexburgh,

The MOD function returns the remainder from a division, you can get
the decimal portion of a number by dividing by 1... if your value was
in A1:

=MOD(A1,1)

Dan E

"rexburgh" wrote in message
...
Can someone help?

I need to know if there exists an excel function for use to return
decimals
only in any given numer.

Example:

1241,17 to be displayed/returned as 17

18,875 to be displayed/returned as 875 etc.



Thanks for any help on this issue.

The OP's requirements was to make 1241,17 into 17, not 0.17, and 18,875 into 875. Can't
possibly imagine what good that would do for the environment, but this modification should
do it whithout annoying questions:

=VALUE(MID(TEXT(MOD(A1,1),"@"),3,16))

Best wishes Harald
Followup to newsgroup only please.

"Dan E" wrote in message
...
Rexburgh,

The MOD function returns the remainder from a division, you can get
the decimal portion of a number by dividing by 1... if your value was
in A1:

=MOD(A1,1)

Dan E

"rexburgh" wrote in message
...
Can someone help?

I need to know if there exists an excel function for use to return decimals
only in any given numer.

Example:

1241,17 to be displayed/returned as 17

18,875 to be displayed/returned as 875 etc.



Thanks for any help on this issue.