Hello, welcome to another #formulafriday blog post for some more formula fun on a Friday – why not?. Today let’s take a look and explore the issue of Excel rounding or not rounding numbers. You may have come across this in Excel especially when working with currency values. Rounding your values may work in some scenarios, but you may NOT want to round up figures in some circumstances. Take a look at an example, you may have a yearly charge of a service, which is split between 12 monthly payments. See my screenshot below.

Excel Rounding- Do You Want It?

The example above Excel has rounded the monthly charge to 10. This is not pricing that the sales department want to promote the service at in their online advertisement. Excel will automatically round the numbers if the column is not wide enough to display the entire number, or when it’s format is set to show a smaller number of decimal places than the original number contains.

If you enter a number into a worksheet cell that has General formatting, like all cells in a new worksheet, the number will be rounded so it fits into the cell. In the case of our promotion of monthly service the display in the cell is 10 but in the formula bar, you can see that the actual entire number is still available in full.

To toggle between seeing the formula and the result of the formula in the formula bar you can click into the formula bar then hit the F9 button. Hit the ESC key to return to the display to the formula again.

Using The TRUNC Function.

The TRUNC function returns a truncated number based on a given number of digits. The Syntax of TRUNC is

=TRUNC (number, [num_digits]) where

number = the number to truncate

num_digits = an optional argument with which to determine the precision of the truncation. The default is 0.

TRUNC removes the fractional part of the number and returns just the integer. For example,

4.9 would return 4

5.25 would return 5

As you can see this is a lot different to the automatically rounded numbers that Excel usually displays. So, if you want only the integer part of your number use TRUNC. Back to our online sales example. The sales department do want to use the monthly payments of 10.00 that Excel rounds to automatically instead we have to force the 9.99 offer they want to market to their online customers. Ok, so that is no problem for us now. As well as just returning integers TRUNC can also be used with the optional argument num_digits. This will return a specified number of decimal places, again with no rounding.

The Solution!

So we can use the TRUNC formula for our problem as we can specify that it uses 2 as the optional argument as the number of digits.

Our formula would look like this

Solved!. We can keep the sales department happy.

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

Do You Need Help With An Excel Problem?.

I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.