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.


Post your answer or comment

comments powered by Disqus
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.

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.



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


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!

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.


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

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 
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!

Any ideas please?


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.

Thanks for your help!

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!

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.


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

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.

Dist %,.1,0,.04,0,0,0,0,0,.87,0

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

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

Hi Guys

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


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,

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

Thank you for your help!!!


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.

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.

Thanking you in advance.

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

how do i round to the nearest 0.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

Thank you all,
Confused Man

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:

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:


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:


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!

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.

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.

Your help is appreciated!

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?

Please use the following cells:
A1 = Date Submitted
B1 = Date Approved
C1 = Length of Time: Days
D1 = Length of Time: Months
E1 = Length of Time: Years

Thank you very much!

No luck finding an answer? You could always try Google.