Round to nearest quarter...

[dkr1]

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!

 

[tactps]

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)))))

 

[Smitty]

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

 

[Ian Mac]

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

 

[NateO]

http://www.mrexcel.com/td0024.html

=ROUND(a1*96,0)/96

 

[Ian Mac]

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

Goto:

Tools>Add-Ins> Then chack Analysis ToolPak
OK

Also if your values are still Time Values use:

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

Regards

 

[CraigMcKay]

=CEILING(A1*8,2)/8

<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" align="right" height="20" width="64">0.19</td> <td class="xl63" style="width:48pt" align="right" width="64">0.25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.1</td> <td class="xl63" align="right">0.25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.25</td> <td class="xl63" align="right">0.25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.3</td> <td class="xl63" align="right">0.50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.45</td> <td class="xl63" align="right">0.50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.5</td> <td class="xl63" align="right">0.50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.55</td> <td class="xl63" align="right">0.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.6</td> <td class="xl63" align="right">0.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.7</td> <td class="xl63" align="right">0.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.75</td> <td class="xl63" align="right">0.75</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.8</td> <td class="xl63" align="right">1.00</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">0.9</td> <td class="xl63" align="right">1.00</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1</td> <td class="xl63" align="right">1.00</td> </tr> </tbody></table>