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.

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

If you want to round a value to some multiple of a whole number, you'll want to become familiar with the MROUND function. ...

Discover MoreWhen preparing financial reports, it may make your data easier to understand if you round it to the nearest multiple, ...

Discover MoreHow to round values half way between two boundaries is apparently a topic of contention in some sectors of the ...

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

2017-12-03 23:49:59

Clifton

2017-08-21 04:36:41

John Smith

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

=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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2015-11-29 08:41:22

Michael (Micky) Avidan

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

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

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

A formula for function for that?

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.

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

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments