Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

rounding nearest half

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Rounding to nearest half

Rounding to nearest half

ResolvedVersion 2010

Sophie has attended:
Excel Intermediate course

Rounding to nearest half

Hello,

I recieve data, and then have to round the data to the nearest half day (so 1 day, 1.5 days, then 2 days, etc.). How do this? I know how to round to one decimal place, but the I have to round it up and down myself!

Thanks!

RE: Rounding to nearest half

Hi Sophie

Great question! You are right in thinking ROUND won't do what you need. To round to half days you can use a different function called MROUND.

Here's an example to see how it works:

A B
Days To half days Formula
1.7 1.5 =MROUND(A2,0.5)
1.2 1.0 (copy down)
5.8 6.0
7.3 7.5
8.9 9.0

If you want to round to quarter days then use 0.25 rather than 0.5 as the multiple.

Does that work in your case or do you need 1.7 to change to 2.0 days rather than 1.5 days?

Regards
Doug

Doug Dunn
Best STL




RE: Rounding to nearest half

Hi Doug,

Thats great, thank you! You have no idea how much time (and patience) you have saved me!

Sophie

Excel tip:

Select blank cells automatically

Get Excel to find any blank (empty) cells in a region for you by:

1. Selecting the appropriate region from your spreadsheet.

2. On the menu bar, go to Edit - Go to.

3. Click the 'Special' button, then select Blanks and click OK.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.