Rounding to the nearest 6 degrees

New Member
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!

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
=CEILING(D2/6,1)*6 where D2 contains 33.24.

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.

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!

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!

Replies
2
Views
243
Replies
10
Views
493
Replies
10
Views
245
Replies
4
Views
167
Replies
1
Views
332

1,196,098
Messages
6,013,441
Members
441,766
Latest member
ixruiz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back