trevor1940

asked on

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:W
```

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?

Last Comment

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"),":",TEXT(G2,"00"),":",TEXT(I2,"00"),":",B2,"/",TEXT(M2,"000"),":",TEXT(P2,"00"),":",TEXT(R2,"00"),":",K2)

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.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

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