Decimal value help

[Damo10]

Hi,

I am looking for a formula that will give me the decimal value from a number. i.e. if cell A1 contains 10.2 then the formula result in cell B2 will be 0.2

Regards

 

[jeffreybrown]

Try...

=MOD(A1,1)

 

[Rick Rothstein]

Besides the MOD method that Jeffrey showed you, there is also this old standby formula...

=A1-INT(A1)

 

[Damo10]

Thankyou both,

They both sort of give me what i need but sometimes the result end up being 1.0, I only require the number after the decimal point.

Regards

 

[jeffreybrown]

Can you post what you started with before you ended up with the 1.0?

 

[pgc01]

Hi

Both formulas should give you just the decimals.

My guess is that you have something like 0.9999999999999... that is being displayed as 1.0 because it's rounding.

Try the following: format the cell as number with 15 decimals. What does the cell display?

 

[Damo10]

Hi,

Yes that worked when i changed the decimal places to 15, how can i then just get the first number after the decimal point as that is the only number i require?

Regards

 

[pgc01]

Try:

=MOD(INT(A1*10),10)/10

Remark: this assumes that in your previous calculations there are no rounding errors.

 

[Damo10]

Thats it, thanks for your help

Regards

 

[circledchicken]

Alternatively this should produce the same result:

=VALUE("0."&IFERROR(MID(A1,SEARCH(".",A1,1)+1,1),0))

Here, the number in bold can be toggled to the number of digits you want after the decimal point:

e.g.
=VALUE("0."&IFERROR(MID(A1,SEARCH(".",A1,1)+1,3),0))
and
=MOD(INT(A1*1000),1000)/1000
would be equivalent.

Note: IFERROR is available in Excel 2007 and above.