Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Excel Formula Help - Rounding to the nearest £1.00

Here's a quick and simple Microsoft Excel tip for rounding numbers up or down to the nearest multiple, in this case £1.00.

As seen in the example below, we have a number of supermarket tills and their corresponding monthly takings. Our first step is to create new column entitled, To the nearest £1.00. We then enter this formula in the cell below: =ROUND(A1/1,0)*1 and press return.

The final step is to copy down to fill each cell with its corresponding rounded up/down value.

The formula first divides the original value by 1 and then multiplies the result by 1. Changing the appropriate values in the formula results in other rounding values for example:

=ROUND(A1/.25,0)*.25 rounds to the nearest 25p

=ROUND(A1/.50,0)*.50 rounds to the nearest 50p

=ROUND(A1/100,0)*100 rounds to the nearest £100

..and so on.

To find out more about the ROUND function, speak to our expert consultants or visit the Microsoft Excel Help pages here.