Rounding to the nearest 6 degrees

[AdamRick]

I am working with some spatial data that is stored as degrees, minutes and seconds. I need to round these to the nearest 6 seconds. Each value will look something like this: 23 45' 33.24''

I need the final value to be a multiple of 6, (33.24 = 36). Is there an easy way I can do this?

Thanks in advance for any help!

 

[tusharm]

=CEILING(D2/6,1)*6 where D2 contains 33.24.

 

[jbeaucaire]

Wouldn't the need to round to the "nearest" 6 seconds include some rounding down? I would offer:

=MROUND(D2,6)

...where D2=33.24 would round up to 36, but D2=32 would round down to 30.

Be sure to turn on the Analysis Toolpak in Tools > Addins.

 

[AdamRick]

Thanks all! The solutions work well for what I need... I do have however have one question - is there a way to deal with a situation when it rounds to 60 seconds? Such that it adds a minute. E.g. 23 45' 57.24'' would result in 23 46' 00''

Would be great to have that working!

Thanks!

 

[tusharm]

This request is quite different from the 1st one, isn't it?

You should also clarify whether you want to round, which will yield results that are > starting number and results that are < starting number, or whether you want to *add* up to the next minute. The two solutions you were given cannot both work the way you want. One rounds up, the other rounds.

In any case, you can adapt my previous solution to whatever you want. If you want to round (or round up or round down) to whatever metric, use that function (CEILING for roundup, ROUND for round, and FLOOR for Round down) and that metric in place of the 6's.

Thanks all! The solutions work well for what I need... I do have however have one question - is there a way to deal with a situation when it rounds to 60 seconds? Such that it adds a minute. E.g. 23 45' 57.24'' would result in 23 46' 00''

Would be great to have that working!

Thanks!