Free Microsoft Excel 2013 Quick Reference

# Round to nearest 100?

How do I add a formula to excel to round a number to the nearest 100?

Eg 1445 would need to be rounded down to 1400 or 1175 would become 1200?

The numbers are already integers. Floor and ceiling formula seem almost to work, but I need to to automatically round up if 50 or over and round down if 49 or under.

Thanks

## Related Results

### Rounding to nearest hundred dollar in Excel

User wants to round up to nearest \$100 dollar figure if it's over 50, or down
to nearest \$100 figure if it's under 50. For example, if they enter dollar
figure of \$1234.00, they want it rounded down to the nearest \$100 dollar
amount, which would be \$1,200.00. Thanks.

### Round to nearest 5

Hi, cant find a formula that round to nearest 5. Just rounddown and round up. I. e if the value is from 130,01 - 132,49 it will be 130. If the value is 132,50 - to 134,99 it will be 135.

Thanks!

Norway

### Rounding to nearest 15 minutes

First please excuse me, I am a complete noob to excel. I have downloaded a punch in time clock from another user " Alex17", great job by the way. I was wondering on how to apply some certain rules this. I would need the times to round to the nearest quarter. Let's say someone punched in @8:01AM or any time up to 8:07AM, I would need it to round to 8:00AM, if they punched in from 8:08AM up to anytime to 8:14Am, I would need that to round to 8:15AM or if someone punched in @ 8:23AM it would round to 8:30AM....etc. I attached the form. Any help would be greatly appreciated. Thank you.

I need these rules to apply

7:00 - 7:07 round down to 7
7:08 - 7:15 round up to 7:15
7:16 - 7:22 round down to 7:15
7:23 - 7:30 round up to 7:30
7:31 - 7:37 round down to 7:30
7:38 - 7:45 round up to 7:45
7:46 - 7:52 round down to 7:45
7:53 - 8:00 round up to 8

or if this makes more sense

7:00 - 7:07 round down to 7
7:08 - 7:15 round up to 7.25
7:16 - 7:22 round down to 7.25
7:23 - 7:30 round up to 7.5
7:31 - 7:37 round down to 7.5
7:38 - 7:45 round up to 7.75
7:46 - 7:52 round down to 7.75
7:53 - 8:00 round up to 8

### Round to nearest whole number ending in 9

Hello,

I am trying to find a way in excel to round to the nearest whole number ending in 9. For example:
158 should round to 159
296 should round to 299
212 should round to 209

Can anyone help? Thanks!

### Round To Nearest Even Number. Bankers Rounding

We are trying to find the correct formula to round number to the nearest even number. In the attached example, 0.105 should round to 0.10 since the zero to the left of the five is even. If the problem was 0.115 then the answer would be 0.12 since the one to the left of the zero is odd.

Any ideas on the proper setup? I have included several examples which we have tried but they don't do exactly what we want.

Thanks
Phillip

### Rounding To Nearest x

I wish to apply a rounding rule that will correctly round up or down to the nearest 5 or 0.

I tried using ceiling and floor and am almost there, but not quite.

Rule is to round up or down to nearest 5 or 0
```
VB:

original	plan	c	f	check	rounded	rule	will use
634	should be 630	635	630	f	634
634.4	should be 630	635	630	f	634	3	f
634.5	should be 635	635	630	c	635	2	c
634.6	should be 635	635	630	c	635	2	c
635	should be 635	635	635	c Or f	635	1	f
635.1	should be 635	640	635	f	635	1	f
635.5	should be 640	640	635	c	636	4	c
636	should be 640	640	635	c	636

rules:
1	if rounded = f Then use f
2	if rounded = c Then use c
3	if rounded < original Then use f
4	if rounded > original Then use c
5	if rounded=original..and ???

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

In the example,
original is the original value
plan is what it should be
c is ceiling value
f is floor value
check is what it should be, c or f
rounded is the rounded value

rules are what I think I need to create
and I have associated these - ish!

Thanks

### Rounding to nearest 9 or 5

I control pricing for our stores and would like to be able to enter a cost with an formulated output for a retail. This is what i use now;

=SUM(D15/0.7) D15 being the cost this deliveries me a retail price at 30%

How can I make the formula round my final figure to \$3.99 if the above outcome was \$3.96. I would like everything in that formula to round up to the nearest 9. Another example \$3.02 should round to \$3.09.

### Rounding to nearest 5

How do I round a number to the nearest 5? For example if a number is 23.4 I want excel to round to 25. Is this possible?

Thanks for your help. Excel challenged!

### Rounding to nearest dollar

How do I round \$452.57 in cell A1 to \$453.00? I want to format the cells to round to the nearest dollar and leave the .00 cents.

### Round to nearest hundred to thousand

Hi

I would like to round numbers up to nearest hundred or thousand.
Ex \$364 to \$400 , \$1275 to \$1300

### Percent Rounding to sum 100%

I have a set of data that is meant to distribute a certain number of items to different groups.

I have 10 groups, some will get more than others depending on previous usage. The problem is that I need the percentages to be in whole numbers and the total percentage needs to be 100%. I tried rounding but it doesn't work. Here is an example from one item's line.

Group#,1,2,3,4,5,6,7,8,9,10
Dist %,.1,0,.04,0,0,0,0,0,.87,0

These are rounded and it comes out to be 101%.

### How: Round to nearest 25

I have formulas which give answers like 169, 181, 1131

Would someone please explain how to round the answers to 175, 175 1150
ie round up or down to nearest 25

### Round to nearest 1000 VBA

Hi Guys

Anyone know how to round to the nearest 1000 using VBA.

Thanks

### Rounding to Nearest 250

I am want to round entries in a spreadsheet to the nearest 250. For
example, 510 would be rounded to 500; 3,810 would be rounded to 3,750,
etcetera.

Do I use the Rounding function or a series of IF statements?

Scott

### Rounding to nearest eighth in fraction form

Hello, I have a worksheet that converts metric to standard, and also rounds to the nearest eighth of an inch in decimal form. If possible, I need it to round to the nearest eighth in fraction form, (ex. .125 to 1/8, .25 to 1/4 etc...). I tried to change the formatting to fractions, but it doesn't seem to work. I think it is because of the formula I am using, =INT(CONVERT(A2,"mm","ft"))&"' - " &ROUND(MOD(CONVERT(A2,"mm","in"),12)*8,0)/8 & """"
Is there something else that can be placed in the formula or anything else that can be changed in formatting to achieve fraction form to the nearest 1/8 ? Lengths should be placed in column A in millimetres and results in feet and inches should show in column B. I have attached an example file. Please help.

### Bracket for negative & round to nearest thousand

Let's say we have -567,987

I want it to be showed in bracket and round to the nearest thousand:: (568,000)

All I know now is that for the bracket we may use #,##0;(#,##0);0

and ##,##0,",000" to round number to nearest thousand.

I had try to alter & combine it, no Luck.

### Rounding to nearest integer

I have searched many many places and still cannot find my answer. I have a
worksheet that I am calculating workers minutes allowed - I used
=SUM(B322:B323) which is correct, then I have to total hours per day - I need
to total using the number obtained from Min Allowed divided by 60 then
calulated to the nearest half
ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8

I need the formula for rounding to the nearest half hour

Any help is much appreciated as I'm really lost

### Rounding to nearest 0.5 in excel

how do i round to the nearest 0.5

### Round to nearest 5?

Hello all,

I have a lot of data and I want to have it all round to the nearest "5"....
so that weather it is a whole number or one with a decimal it always
contains the same number of digits but always ends in either 0 or 5.

For example:
10371.80 stays the same
1204.98 rounds to 1205.00
696.51 rounds to 696.50
etc.

Thank you all,
Confused Man

### Round to nearest .05 or .09

Below is my current formula. Right now I have it rounding the results to the nearest .09. I would also like to have it round to the nearest .05 as well.

In other words, to be more exact: I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.

Here is my current (incredibly long) formula:

```

```

### Rounding to nearest 1/16"

Good morning all!

I am a new user in Excel and I've used it very few time. Most I've done is add columns. I'm trying to figure out formulas and what not now. I'm actually using this for the business.

I have a function right now that goes like this:

=sqrt(cell^2+cell^2)

Its pythagorean theorem. It work but I want to round my answer off to the nearest 1/16" (closest up or down). I typed in this formula:

=mround(sqrt(cell^2+cell^2),0.0625)

It doesn't work obviously. But say my answer is 12.078125, I want it to round to 12.0625. Say my answer is 12.109375, I want it to round to 12.125.

If anyone needs more information I'll be glad to share it. I'll be asking a million questions on this forum and I tried using the search function and came up with nothing about rounding with fractions (that I could use).

Thank you so much!

### Rounding to nearest half hour

Does anyone know how to create a formula that will round time values to the nearest half hour and show only one decimal place? For example....1.25 hours would round to 1.5 hours.

### Round to nearest thousand

Is there a custom format I can use to round to the nearest thousand? Eg if I
have 104580 I want it to round to 105000. I tried #,##0 but it takes off the
last three 0's which I need to display.

--
Thanks,
GillianHG

### Calc # of d, m, y, then round to nearest fraction

Hello, I have been reading a lot of the Q&A here, but I haven't found one
that worked perfectly for my spreadsheet. I even went to the Pearson site,
but I didn't have any luck with the formulas.

GOAL: Multi-function cell
Calculate the number of days, months, and years, then have each result
displayed as a specific number (rounded to the nearest fraction, preferably
to the 100th or even as a quarter fraction).

Can anyone provide the exact formula for me to use?