How to round to fractions in Excel 2003

How to round to fractions in Excel 2003

Samuel Murray
Netherlands
Local time: 12:10
Member (2006)
English to Afrikaans
+ ...
 Oct 1, 2013

Hello everyone

I want Excel 2003 to round down hours to the nearest 0.5. What formula do I need for that?

In other words:
* if the cell's value is 1.8, I want to round it to 2.0.
* if the cell's value is 1.7, I want to round it to 1.5.
* if the cell's value is 1.6, I want to round it to 1.5.
* if the cell's value is 1.4, I want to round it to 1.5.
* if the cell's value is 1.3, I want to round it to 1.5.
* if the cell's value is 1.2, I want to round it to 1.0.
* if the cell's value is 1.1, I want to round it to 1.0.

Thanks!
Samuel

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

Marie-Helene Dubois
Spain
Local time: 12:10
Spanish to English
+ ...
 MRound function Oct 1, 2013

Hi Samuel,

You need the MROUND function for that.
You need the original figure in one cell and the new (rounded up or down) figure in another cell for this to work.

Here is a really good and clear tutorial that shows you the formula to use:

You would just use MROUND([cell number with original value],[rounding value, in your case 0.5]). You can then just drag this formula down to apply it to all cells.

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

Marie-Helene Dubois
Spain
Local time: 12:10
Spanish to English
+ ...
 here's how it looks on my Excel Oct 1, 2013

=MROUND(A1;0,5)

as an example. I'm using 2007 and the non-English number format though so you may need to adjust accordingly.

Oh and I forgot to say, make sure you format the results column to show at least one decimal place or Excel will round up/down even further.

[Edited at 2013-10-01 14:17 GMT]

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

Attila Piróth
France
Local time: 12:10
Member
English to Hungarian
+ ...
 Another simple option Oct 1, 2013

Multiply by 2, round it to the nearest integer, divide by 2.

Best,
Attila

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

Kirsten Bodart
United Kingdom
Local time: 12:10
Dutch to English
+ ...
 Help function Oct 1, 2013

I think you should also be able to find it in the integrated help function, but About.com is also good.

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

Samuel Murray
Netherlands
Local time: 12:10
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
 Thanks everyone Oct 1, 2013

Samuel Murray wrote:
I want Excel 2003 to round down hours to the nearest 0.5. What formula do I need for that?

Thanks, everyone, for your answers. I did read the help files and suchlike, but I could not figure out which of the many examples applied to my specific situation.

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

To report site rules violations or get help, contact a site moderator:

 Moderator(s) of this forum Maya Gorgoshidze [Call to this topic] Prachya Mruetusatorn [Call to this topic]

You can also contact site staff by submitting a support request »

How to round to fractions in Excel 2003

SDL MultiTerm 2019
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2019 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2019 you can automatically create term lists from your existing documentation to save time.

TM-Town

Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.

P.O. Box 903
Syracuse, NY 13201
USA
+1-315-463-7323
ProZ.com Argentina
Calle 14 nro. 622 1/2 entre 44 y 45
La Plata (B1900AND), Buenos Aires
Argentina
+54-221-425-1266
ProZ.com Ukraine
6 Karazina St.
Kharkiv, 61002
Ukraine
+380 57 7281624