# Modified Rounding

by Allen Wyatt
(last updated May 26, 2021)

Samantha needs a formula that will round a value based on whether it is less than or equal to .25. So, for instance, 3.24 would round down to 3 and 3.25 (or greater) would round up to 4.

Rounding at the normal place (.5) is rather easy in Excel, as the program provides the ROUND worksheet function. When you want the "break point" for the rounding to be something other than .5, you'll need to rely on a formula and not on a simple worksheet function.

There are (literally) dozens of formulas you can devise to get the result you need. As with many things in Excel, the solution depends on the nature of your data. If the value to be rounded is always going to be positive, then the following formulas will work just fine. (I didn't include these formulas in any particular order other than sorting them alphabetically.)

```=FLOOR(A1+0.75,1)
=IF(A1-INT(A1)<0.25,FLOOR(A1,1),CEILING(A1,1))
=IF(A1-INT(A1)<0.25,INT(A1),INT(A1)+1)
=IF(A1-INT(A1)<0.25,ROUNDDOWN(A1,0),ROUNDUP(A1,0))
=IF(INT(A1)=INT(A1+0.75),INT(A1),INT(A1)+1)
=IF(MOD(A1,1)<0.25,INT(A1),INT(A1)+1)
=IF(MOD(A1,1)<0.25,ROUNDDOWN(A1,0),ROUNDUP(A1,0))
=IF(MOD(A1,1)<0.25,TRUNC(A1,0),TRUNC(A1,0)+1)
=IF(MOD(A1,SIGN(A1))<0.25,ROUNDDOWN(A1,0),ROUNDUP(A1,0))
=IF(MOD(A1*100,100)>=25,ROUNDUP(A1,0),ROUNDDOWN(A1,0))
=INT(A1)+IF(A1-INT(A1)>=.25,1,0)
=INT(A1)+IF(MOD(A1,1)>=0.25,1,0)
=INT(A1+0.75)
=ROUND(A1,0) + IF(MOD(A1,1)>=.25,1,0)
=ROUND(A1+.25,0)
=ROUNDDOWN(A1-0.25,0)+1
=ROUNDDOWN(A1+0.75,0)
=ROUNDUP(A1-0.24,0)
=ROUNDUP(FLOOR(A1,0.25),0)
=TRUNC(A1)+(MOD(A1,1)>=0.25)
```

No doubt there could be even more formulas and variations of formulas to do the rounding. If the value to be rounded may include negative values, then you'll need to use a different formula. (Again, these are only sorted alphabetically.)

```=IF(ABS(A1)-INT(ABS(A1))>=0.25,INT(A1)+IF(A1<0,0,1),INT(A1+IF(A1<0,1,0)))
=ROUND(A1+IF(A1>0,0.25,-0.25),0)
=ROUND(A1+SIGN(A1)*0.25,0)
=ROUND(ROUND(A1*2,0)/2,0)
=ROUNDUP(ROUNDDOWN((A1/0.25),0)*0.25,0)
=ROUNDUP(ROUNDDOWN(A1*4,0)/4,0)
=TRUNC(A1+SIGN(A1)*0.75)
```

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10126) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Specifying a Data Validation Error Message

Data validation is a great tool for limiting what can be input into a cell. Excel allows you to specify what should ...

Discover More

Simulating Alt+Enter in a Formula

You can use the Alt+Enter keyboard shortcut while entering information in order to force your data onto multiple lines in ...

Discover More

Encrypting a File Folder

Want to make the data stored on your system more secure? Think about enabling the built-in encryption capabilities of ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

##### More ExcelTips (ribbon)

Rounding in Results

Rounding is a fact of life when it comes to using formulas in a worksheet. Sometimes that rounding can be a bit ...

Discover More

Rounding to the Nearest Half Dollar

When working with financial data, it's easy to round values to the nearest dollar. What if you want them rounded to the ...

Discover More

Rounding To the Nearest Even Integer

Do you need your numbers to be rounded to an even integer value? How you accomplish the task depends on the nature of the ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight more than 6?

2021-05-26 09:15:41

David Bonin

I noticed the two questions by Steve Miller and James. I hope they found their answers long ago.

Their questions seemed like they may be of interest to others, so let me offer this...

Building off Steve Miller's example, if OLF and AREA are defined in cells A1 and A2, then he could put this formula into A3:
= MAX( ROUNDDOWN( A2 / A1 ), 1 )

This formula will calculate what he wants and never less than a value of 1.

James' formula would be wrapped in a       = MAX( <<his rounding formula>>, 0.25 )

There are two faults with the above formulas. They can produce answers when none are warranted, and they can produce divide-by-zero errors when the denominator is missing or zero. Both cases are annoying and both can cast doubt in others' minds when using your work.

Here is a workaround that I would use:
= IFERROR( IF( COUNT( A1, A2 ) = 2, MAX( ROUNDDOWN( A2 / A1 ), 1 ), "n/a" ), "n/a" )

It may seem a bit complex at first glance, but notice the original formula in the middle. I wrapped that with an "IF( COUNT( A1, A2 ), ... , "n/a" )". That merely says to calculate the formula if we have two numeric input values. If not, then we return the value "n/a".

The "IFERROR( ... , "n/a" )" is there to return the value "n/a" if the formula evaluates to an error. Wait, what? Well, if the value of A1 is zero, then the calculation returns a divide-by-zero error. while a user may no enter a zero in A1, it's possible that A1 may be the result of some other calculation that returns a zero.

2020-06-08 11:00:15

Izabela

Hi Allen,

I needed to round down numbers including 0.5 and round up from 0.6.
I ended up with following formula =IF(A1-INT(A1)<0.6,FLOOR(A1,1),CEILING(A1,1)) .

Thanks a million,
Izabela

2020-05-04 13:15:10

Steve Miller

Hi, I've been hunting high and low through the internet and cant seem to find an answer, so here's hoping....

I'm looking for a formula that divides an area into an occupant capacity:

Area/Occupancy Load Factor (OLF), rounded down to nearest whole number. so formula = Rounddown(area/OLF)

However, if the return is between 0 and 1, I get a return of 0. I need this to be 1

I've tried using functions MROUND, ROUNDDOWN, FLOOR and FLOOR.MATH.....

examples are:

OLF = 20
Area = 25
ROUNDDOWN(25/20) = 1

OLF 20
Area = 12
ROUNDDOWN(12/20) = 0 .... I need the answer to be 1 (mimimum)

2017-12-03 23:49:59

Clifton

Thanks again Allen for a comprehensive and well explained solution to an Excel problem. In 10 seconds I applied this to my own specific needs. Thanks for being out there, and sharing your knowledge and skill with others.

2017-08-21 04:36:41

John Smith

I carried on searching and found this:
https://blogs.msdn.microsoft.com/ericlippert/2003/09/26/bankers-rounding/

Now I know the name of the convention I have used for half a century, Bankers' Rounding. This page also gave me some surprising information which others may find useful: the Round function typed directly rounds 0.5 upwards. However, Round in a User Defined Function uses Bankers' Rounding and rounds to the nearest even integer.

So I have been able to construct this beautifully simple entry in an addin file:

Public Function RoundEven(Num As Double)
RoundEven = Round(Num)
End Function

I am very grateful for the responses to my question; they helped me to recognise the significance for my purpose of the differences between Round and Round (in different Excel contexts).

2017-08-18 08:28:16

John Smith

I revisited the original post and think I have been able to gild nir liberman's refined gold with this formula:

=IF(A3-INT(A3)<>0.5,ROUND(A3,0),IF(ISEVEN(A3),ROUNDDOWN(A3,0),ROUND(A3,0)))

Thanks for pointing me at the ISEVEN function. This formula now gives me what I want for the "round 0.5 to the nearest even number" objective. I flirted with TRUNC instead of INT, but INT seems to work better with negative numbers for my purpose.

Although I have a couple of extremely simple functions in my personal.xlsb or an xlam file, I am hopelessly incapable of writing one to make this formula into a function, called ROUNDEVEN, which will be available to all workbooks. I should be most grateful if someone could show me the wording needed.

2017-08-17 04:31:17

Peter Atherton

James

Here is another one

=MROUND(A1,0.25)+IF(MROUND(A1,0.25)-INT(MROUND(A1,0.25))=0,0.25)

2017-08-16 03:33:52

Michael (Micky) Avidan

@James,
Only if I understood you correctly - try:
=ROUND(A1+0.25,0)+(ROUND(A1+0.25,0)<0.25*A1)
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

2017-08-15 18:27:20

James

Hello,
I need to round to the nearest 0.25 with a minimum value of 0.25.
In other words I need to round either up or down by the 1/4, but never down to 0.00.
Does this make sense?
Thanks.

2015-12-12 05:45:46

Michael (Micky) Avidan

@Dave,
I'm aware of all those Q&A sites for Excel but Allen's site can get a little bit more sophisticated by enabling attachments and if not - at least by adding a very clear(!) and bold(!) announcement that the OP should attach his WB with examples.
I might agree with the first part of your saying (quote): "... and perhaps don't have the base knowledge..."
BUT I can't agree with: "...or time to carefully craft a precise question."
One who needs help should be clear and detailed even if he thinks it is not very needed.
----------------
I, myself, am an Autodidact person as far as Worksheets are concerned (Started with "VisiCalc" -> "Lotus 1-2-3" > "Quattro-Pro" in the early 1980th.
My programming skills are: BASIC, FORTRAN, COBOL & PASCAL).
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-12-11 10:13:43

Dave

@Micky

Perhaps you hit upon the crux of the matter. You (I presume) and I are very detail-oriented. We try to provide a very clear and detailed answers to a very specific and precise questions.

On this site, many of the readers are not as particular as we are, and perhaps don't have the base knowledge or time to carefully craft a precise question. Questions we perceive as fuzzy may seem very clear to them. No matter. As long as their question is at least generally understood and most readers come away a little smarter, then this site has served its purpose.

There are plenty of very specific Q&A sites for Excel that users can turn to if they need a deeper level of help.
_____

BTW, to provide a little background (to be taken as info, not bragging), I am a mechanical engineer who also happens to have degrees in electrical engineering and mathematics. I've been programming for nearly 40 years, starting with Basic and Fortran, and then c- and lisp-oriented CAD languages. I'm self-taught in Excel and have using it heavily for 20 years. Like anything, if you do something long enough, you tend to get really good at it. I think Excel is a wonderful tool. I wish I had it in the 1980s when I was writing 10,000 line Fortran programs. It would have saved me man-years of time and provided much more user-friendly results.

2015-12-10 05:26:50

Michael (Micky) Avidan

@Dave,
This was an excellent example why I - in the Israeli leading Excel Forum, that I manage for the last 10 years - always insist that the OP will attach his Workbook with ALL(!) possible raw data examples and hand typed expected results.
(As always, confidential data can be changed/replaced).
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-12-09 11:15:25

Dave

@Micky,

Please don't be that way. There are plenty of places on the internet to flame people for the slightest perceived infraction of personal pecadilloes. This should not be one of them.

@Everyone,

What John Smith is referring to is what I believe is sometimes called "military rounding". In the more generic sense, the goal is that the last displayed digit should be an even value if there is only one discarded digit and it is exactly equal to a 5.

It is a nice, consistent system even if it does show a slight bias towards even terminal digits. It can readily be extended to rounding to both the right and left of the decimal point.

2015-12-02 14:44:16

Chris C

In response only to the original John Smith comment -- To my way of thinking, the logic behind this is only pertinent to the cases in which only 2 or 4 [or 8... ] decimal options are possible in the number set, eg: a) x.0, x.5; or b) y.0, y.25, y.5, y.75; ... Assuming that is true, and assuming there is an equal opportunity for any decimal to be the case for any particular number, then you would be changing more numbers in the set to a larger value than to a smaller value; so the odd/even technique would be best.

However if there are other possible decimal values (for example 10 or 100), the method would become moot, as there are equal number of chances to round up or down.

2015-12-01 06:49:59

John Smith

Whoa back gentlemen! This exchange might make me, and any other would be information seeker, afraid to post a question.

The article prompted me to seek an Excel solution to an Excel problem. Nir Liberman provided a perfect answer, again thanks for that.

William of Occam would see success here: I asked, Nir Liberman answered, problem was solved. I am content.

2015-12-01 05:20:54

nir liberman

@Stewart Borowski + John Smith

I must agree with Michael (Micky) Avidan that the initial post was not very clear.
In my comment I used the example in the post, and not the question as a refernce to my answer.

2015-12-01 04:33:31

Michael (Micky) Avidan

@Stewart Borowski,
In 2 words: "NO COMMENT".
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-12-01 00:33:21

Stewart Borowski

@ Michael (Micky) Avidan,

Please clarify exactly what you disagree with?

John Smith has stated that you have misunderstood his request. Given that he is the author of that request, it is his prerogative to define who has or has not understood what he was asking for. It is impossible for you to "disagree" with that statement of perspective.

I CAN agree that it was not clear that his initial statement/request ONLY pertained to 0.5 values, and was not relevant for any other fractions (0.2, 0.3, 0.6, 0.8 etc.), however that specific aspect was clarified in a follow up comment (which was posted some 5 hours before you 'disagreed', by the way?).

You also comment, and I quote (no Inception references please! :-) : "The sentence (and I quote): "...each to the nearest whole number whether(!) odd(!) or even(!)" ".

I can find no comment where John Smith has written what you have quoted. His references to odd and even are in separate posts. I also presume the ! following each word is your own inflection?.

I too have never until now heard of the concept of rounding exact halves to the nearest even number. But that does not automatically invalidate it, either as something 'once taught' or as something still valid. Personally, it makes good sense when used in larger data-sets, as it stops the .5 value over-weighting towards higher numbers over time/volumes.

As an MVP and Answers forum author & Mod, I'm certain that you would want to help provide clarity and understanding on topics like these?

Stew.

2015-11-30 10:57:09

Michael (Micky) Avidan

@John Smith,
Correction of a small TIPO:
I meant to ask "Where did you mention: "...to the nearest EVEN OR(!) ODD(!) number" ?
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-11-30 10:53:48

Michael (Micky) Avidan

@John Smith,
I'm really sorry but I strongly disagree with your statement regarding my "misunderstanding".
The sentence (and I quote): "...each to the nearest whole number whether(!) odd(!) or even(!)" - is strictly "new" (to me).
Would you be so kind to check (again) your original question in order to tell me/us where, exactly(!), did you mention the word "OR" (referring to the "odd OR even" part of your currently intention/correction)?
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-11-30 09:24:34

Glenn Case

You can also use MROUND. The syntax is

=MROUND(num, multiple)

where “num” is the number to be rounded, and “multiple” is the nearest multiple. So to round 437 to the nearest 25, you would use

=MROUND(437, 25)

which produces a result of 425.

2015-11-30 05:57:29

John Smith

I am sorry that you misunderstood my request. What you quote is true but it is only part of the problem. The illustrations you give are not relevant to .5 values, the subject of my request.

I asked specifically about .5 values. I expect values up to .49 to round down and .51 and over to round up, each to the nearest whole number whether odd or even.

Excel's method of always rounding .5 UP will, over a long series, result in a bias. The convention I was taught ensures a balancing. (It would be just as effective if all .5 values rounded to the nearest ODD number, balance is the important bit.)

Nir Liberman's solution works to resolve my question.

2015-11-30 05:39:36

DaveS

@Michael (Micky) Avidan
To extend the quote from John Smith's question: "..the convention was for 0.5 to be rounded up or down to the nearest EVEN number". So he did not refer to "all numbers", only to the specific case of the fraction 0.5, and for the stated reason.

2015-11-29 14:30:03

Michael (Micky) Avidan

John Smith,
If so, then you must, urgently, rephrase your question.
You mentioned and emphasized (in capital letters) that - and I quote: "...to be rounded up or down to the nearest EVEN number".
Mr. Liberman's suggestion DOES NOT round all numbers to the NEAREST EVEN Number.
The nearest EVEN number for: 8.7 is 8 and not 9.
Same goes for: 11.4 which should read: 12 and not 11.
And last but not least: 1234567.2 should result in 1234568 (EVEN) and not 1234567 (ODD).
-------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-11-29 12:56:57

John Smith

Thanks to you both for your suggestions. The one that worked straight away for me is from nir liberman.

I shall turn this into a macro with a Quick Access button.

2015-11-29 08:41:22

Michael (Micky) Avidan

@John Smith,
If I understood you ,correctly, try my suggested UDF (User Defined Function).
-----------------------------------
Function Round_2_Even(Num As Double)
Round_2_Even = CLng(Num * 0.5) / 0.5
End Function

http://screenpresso.com/=yBatd
-----------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

2015-11-29 02:08:10

nir liberman

@John Smith
You can add / substract a small number:

=ROUND(A1-ISEVEN(A1)/10000,0)

add as many zeros as you need to the 10000.

2015-11-28 05:50:57

John Smith

I learned at school (in the 50s but don't tell anyone) that the convention was for 0.5 to be rounded up or down to the nearest EVEN number, so 6.5 would round to 6 and 7.5 would round to 8.

This way, a large number of roundings would balance out the adjustments.

A formula for function for that?

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.