# Thread: Need a rounding function

1. ## Need a rounding function

Hi, everyone. Using Office 2010. I need to find a function that will allow me to direct Excel to round a value to nearest 5, so the following would occur:

Cell entry: 72 Excel rounds to: 70
Cell entry: 76 Excel rounds to: 75
Cell entry: 79 Excel rounds to: 80

Use case: Enter a goal value. Excel then calculates, using percentages, a series of incremental values that are rounded to the nearest 5 value.

I'm trying to stay away from an elaborate VLOOKUP table and hoping there is a way to do this with rounding.

Any help or ideas greatly appreciated!

Thanks!

Val in chilly IL

2. Google to the rescue! In case anyone else needs this...

http://www.consultdmw.com/excel-round-to-nearest-5.htm

3. You can just use a simple spreadsheet function:

=ROUND([cell]/5,0)*5

4. And if you don't feel like memorizing the formula, place it in a user defined function and call it like any built-in cell function

In cell A1 =RoundBy5(B1)

Code:
```Public Function RoundBy5(x As Double)
RoundBy5 = Round(x / 5, 0) * 5
End Function```

5. Yes, but then your workbook contains macros, unless you store the function externally, e. g., in PERSONAL.XLSB or in an add-in.

6. TX,
Yep, that's the idea!

7. I realize this is an old post, but for anyone scanning it later, Excel has a function built-in that does exactly what is wanted.
It is the MROUND() function. For the above rounding, if the value to be rounded is in Cell A1, the formulat to round to the nearest
5 would be =MROUND(A1,5). The rounding value, 5 in this case, can also be a cell entry if there may be a need for it to change.

8. Yes indeed . . . worth mentioning that you have to have the Analysis ToolPak installed for MROUND to work.

9. Not in 2007 or later - it's built-in.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•