Basic Microsoft Excel Functions: ROUND

The ROUND function rounds a number to a specified number of digits.

ROUND will take a number and round it to a user specified number of digits to either the left of right of the decimal point.

Using the ROUND function can make numbers much easier to read in Excel, and also improve the presentation of the document.

Download the accompanying file(s) for this article.

Work through the below guide as we learn how the ROUND function works

=ROUND

This is a very simple function. You tell Excel the number to be rounded and to how many decimal places:

=ROUND(number, num_digits)

Example

Breaking down the ROUND Formula

=ROUND: This is the start of the statement and tells Excel what you would like it to do

(B2: Round the number in cell B2…

,2: …to two decimal places

In other words, round the value in cell B2 to two decimal places. Simple!

Why is this useful?

Using =ROUND is a quick and easy way to display numbers in the same format.

The previous example shows how to round numbers to the right of the decimal point, but what if you need to round them to the left?

Simple, add ‘-‘ (minus) before the number e.g

Even though this version of the round formula exists I’ve never seen it used in day-to-day work.

A great example of how useful the ROUND formula can be is when dealing with exchange rates. We can only pay for items to two decimal places e.g £9.99. But what would £9.99 be worth at a different exchange rate?

At the time of writing, the current GBP to EUR exchange rate is 1.18680275338239. Which is totally impractical. By using the ROUND function, we are able to quickly convert any value into something more meaningful:

But this is different to the above formula! This is the beauty of the ROUND formula, you are able to insert formulas within the formula. Let’s break down what is happening:

=ROUND: This is the start of the statement and tells Excel what you would like it to do

(H3*H2: Multiply the value in H3 by H2. This gives you the (number, part of the formula

,2): Round the number to 2 decimal places

By using =ROUND we can see that €11.85616 rounds to €11.86.

Try changing the values in cell F3 on tab ‘Workable Example’ in the accompanying workbook to see how the rounded values change after entering a different rounding value.

The formula used in column C is slightly different to the ones above as it is locked to cell F3. Having a master cell to hold a value can save you a lot of time as it will instantly change the values in all the cells that are linked to it. An article on using master cells will follow soon.

 

Linked Articles

=ROUNDUP – This article describes the =ROUNDUP formula and how to use it to round-up a set number of decimal places

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Excel Basics

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *