# Round to nearest quarter...

This is a discussion on Round to nearest quarter... within the Excel Questions forums, part of the Question Forums category; Is there a way to return a "sum=(a1:a5) and also round to nearest quarter? My number format is "0.00" I ...

1. ## Round to nearest quarter...

Is there a way to return a "sum=(a1:a5) and also round to nearest quarter? My number format is "0.00"

I am working with military time. Thanks!

2. ## Re: Round to nearest quarter...

This can be done by the following formula (lengthy as you need to round each step of the way)

=ROUNDDOWN(SUM(A1:A5),0)+IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.12,0,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.37,0.25,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.63,0.5,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)<=0.87,0.75,IF(ROUND(SUM(A1:A5)-ROUNDDOWN(SUM(A1:A5),0),2)>=0.88,1,0)))))

3. ## Re: Round to nearest quarter...

Welcome to the Board!

Have you tried =CEILING?

Although working with TIME you might want to check out www.cpearson.com

He's got some great suggestions!

Smitty

4. ## Re: Round to nearest quarter...

Try:

=MROUND(SUM(A1:A5),0.25)

You say your woring with military time but your numbers are 0.00
I'm taking it that you've converted the time to decimal

Regards

6. ## Re: Round to nearest quarter...

Oh yes, the MROUND function is part of the Analysis Toolpak Add-In

Goto:

OK

Also if your values are still Time Values use:

=MROUND(SUM(A1:A5)*24,0.25)

Regards

7. ## Re: Round to nearest quarter...

=CEILING(A1*8,2)/8

 0.19 0.25 0.1 0.25 0.25 0.25 0.3 0.5 0.45 0.5 0.5 0.5 0.55 0.75 0.6 0.75 0.7 0.75 0.75 0.75 0.8 1 0.9 1 1 1

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•