trevor1940
asked on
Converting Decimal Degrees to D:M:S in excel
Can someone explain why I'm getting rounding errors in the attached spread sheet? DDtoDMS.xlsx
I'm expecting column 'T' to look like this
If E3 =0.55 and F3 = "E3*60"/33 so how can G3 = "INT(F3)" / 32?
My maths might be dodgy but 0.55 *60 = 33
Also how do I fix the leading zeros in the output?
I'm expecting column 'T' to look like this
42:33:00:N/001:34:59.88:WIf E3 =0.55 and F3 = "E3*60"/33 so how can G3 = "INT(F3)" / 32?
My maths might be dodgy but 0.55 *60 = 33
Also how do I fix the leading zeros in the output?
To force the numbers to show as a certain number of digits, you can use the TEXT function, your end result formula becomes:
=CONCATENATE(TEXT(D2,"000"
Change the number of 0 in the " " to affect how the number appears.
=CONCATENATE(TEXT(D2,"000"
Change the number of 0 in the " " to affect how the number appears.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rob
Any idea why this happening?
Thanx for your 1 cell solution very neat I had opened up my solution to work where this error was
BTW Latitude can only be 2 digits for degrees only saying
@crystal
How will that help? hasn't the damage already been made at this point?
I don't understand how 42.55 - 42 doesn't = 0.55? is there a format setting some where causing this error?
If you copy E2 and paste values elsewhere, it gives 0.549999999999997 hence why that value multiplied by 60 doesn't give an exact 33.
Change the formula in E2 to:
=ROUND(C2-D2,2)
Any idea why this happening?
Thanx for your 1 cell solution very neat I had opened up my solution to work where this error was
BTW Latitude can only be 2 digits for degrees only saying
@crystal
use the ROUND function on values in column I and R. Internally,
How will that help? hasn't the damage already been made at this point?
I don't understand how 42.55 - 42 doesn't = 0.55? is there a format setting some where causing this error?
Crystal has answered the question as why Excel is doing this, much as it is annoying I don't think there is a lot that can be done about it other than allowing for it with ROUND functions.
ASKER
Thanx for you help and explanation
If you copy E2 and paste values elsewhere, it gives 0.549999999999997 hence why that value multiplied by 60 doesn't give an exact 33.
Change the formula in E2 to:
=ROUND(C2-D2,2)
That sorts it.
I don't see any leading zeros in the output.
Thanks
Rob H