Tip of the week # 611
29 June 2020

Round a number up or down to the nearest half (Microsoft Excel) ^*

I cannot believe it's almost July and we're halfway through the year. And what a year it's been so far. (The other day a friend of mine sent me a picture on WhatsApp stating "2020 - The year your wheelie bin goes out more than you.) But I digress.

Inspired by halves and because I am keeping tabs on the hours I work from home for one of my clients I was looking for an easy way to round my numbers down to the nearest half. Excel has three Functions you might find helpful: FLOOR, CEILING, and MROUND.

Here's how: (See logsheet.xlsx to download an example)

1. Round down: =FLOOR(C2,.5) OR
2. Round up: =CEILING(C2,.5) OR
3. Round up or down: =MROUND(C2,.5)

You can also write 0.5 rather than .5 but you all know by now that I try to be as efficient as possible. ;)

To make it look good, turn it into a table (CTRL + T) with totals (CTRL + SHIFT + T).

By the way, if you ever plan to hire me on a time and materials basis, don't worry... I always round down. ;)

Related tips

1. Quickly format and enable filtering of your data
2. Total the data in a table
3. Calculating a person's age

.

^* Unless stated otherwise, these tips were written for Microsoft Office 2010.