Modified Rounding

by Allen Wyatt
(last updated November 28, 2015)

24

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, and 2016.

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

Running a Macro in a Number of Workbooks

Got a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, as ...

Discover More

Hiding Errors on Printouts

If there are error values in a worksheet, you may not want those error values to appear on a printout. Excel actually allows ...

Discover More

Pulling Initial Letters from a String

When working with names or a different series of words, you may need to pull the initial letters from each word in the ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Rounding by Powers of 10

Need to round a value by a power of 10? You can do it by using the ROUND function as described in this tip.

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

Rounding Up to the Next Half

When processing data it is not unusual to need to round that data in some way. For instance, you may need to round a value ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 seven minus 2?

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).
If Mr. Wyatt won't agree to allow direct attachments - every question poster should upload his/her WB to a File Hosting site and provide a downloading link.
--------------------------
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.

Thank you to those who added their comments, particularly DaveS.


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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.