excel spreadsheet question..rounding numbers??

Question

Hi Guys..can anyone help me with a question pertaining to rounding numbers in EXCEL.. Lets say I have a number..

8.10 for example. I need a formula that rounds this up or down to the nearest quarter. Eg 8.10 would round down to 8.0 whereas 8.2 would round up to 8.25

Any help appreciated.

Answer 1

You have these functions
ROUNDUP
ROUNDDOWN
to round a decimal numbers to the nearest number based on number of digits that you will give to them
While
FLOOR and CIELING functions will round an integer number to the nearest number you specify also

Enjoy my profile, I am the VBAXLMan

Answer 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.



Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
A B
Formula Description (Result)
=MROUND(16, 5) Rounds 16 to a nearest multiple of 5 (15)
=MROUND(-16, -5) Rounds -16 to a nearest multiple of -5 (-15)
=MROUND(2.6, 0.08) Rounds 2.6 to a nearest multiple of 0.08 (2.64)
=MROUND(5, -2) Returns an error, because 5 and -2 have different signs (#NUM!)

Returns a number rounded to the desired multiple.

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Answer 3

Hi, from the UK! You haven't said what these numbers are. If they are replicated rows, within a chart, then there should be no problem in deleting all but the single row you need to keep by drag-selecting the row references. CHECK first, then press delete! Alternatively, you may have used column A as an index for the final printed version and it plays no part in the data in the worksheet. I nearly always use this system, myself. If you do this by entering the number 1 in cell 1A, 2 in 2A, and 3 in 3A, selecting and dragging the 2A and 3A downwards, fills the column with successive numbers down the column. As the chart is extended down the page across the columns, the numbers can be extended in order by selecting the last two numbers to continue the successive numbers. IF the problem is ONLY to do with this type of entry and NOTHING ELSE IN THE CHART IS AFFECTED BY THE SORT CRITERIA, then Column A cell A2 (or lower down) has probably lost its equivalent formula continuity of (=a1+1) It will be quicker to bite the bullet and check the number against the Standard Excel row reference for the correct offset. Allowing for where YOUR reference numbers start, then when you reach an error, select the last CORRECT cell above and drag-fill it down to where the numbers are continuous but out of order. On release, the numbers will reconnect down to the next error. Carry on down, scanning by eye, to the next error where the numbers jump or lose continuity. Select the last correct cell and once again drag-fill downwards. Now you've got the hang of it, AND you are STILL confident the chart is sorted correctly, carry on down to the last-but-one cell in the column on the page, release it and press PG DWN, then RESELECT and continue to drag-fill, page-by-page to the end. Hooray! Job done! Phew! YOU PROBABLY WON'T DO THIS AGAIN! I learned to make a copy FIRST in case just such a glitch happened, did all the sorting and deleted the old sheet afterwards OR put it in an archive folder! I wish you happy results from awizzened old Microsoft-user across the pond!

Answer 4

If the number is in A1, then use this function in another cell:
=ROUND(4*A1,0)/4

Answer 5

You need to use the Round() function.

Answer 6

Make sure that the analysis ToolPak is installed (Tools->Addins menu), and use the mround formula:

Assuming that it's in A1
=MROUND(A1,0.25)

simple as that !