How to Round and Sort Data using Excel Formulas?

chandoo, that’s a good formula to use at work for sorting. Just a variation, you could do away with the helper cells of column B by using : {=SMALL(ROUNDDOWN($B$1:$B$9,0),ROW(B1))}

@chandoo – a couple of points.

Firstly, your formula above has semicolons (;) instead of commas (,) in it, so Excel won’t know what to make of it. This should be:
=SMALL(ROUNDDOWN(B2:B10,0),A2:A10)

Secondly, It’s worth pointing out that the array formula is only required because of the way you’ve structured your formula.
If you were to restructure it so the ROUNDDOWN is performed last, then no array required. I.e. =ROUNDDOWN(SMALL($B$1:$B$9,A1),0)

Thirdly, your statement ‘when entered in the range D3:D12′ could be misunderstood. You might want to make it clearer that the user has to select this range first, before entering the formula in the formula bar and then pushing ctrl + shift + enter. Otherwise they might array enter it into D3 and then copy it down…which also would technically enter it into this range. In which case your particular formula wouldn’t work as structured.

@chrisham – Good improvement, but a couple of things could catch users out with this approach.

Firstly, a clarification…Your formula works if it’s array entered into one cell and copied down – which I’m sure is exactly how you would mean it to be entered. But if it’s entered the same way as for Chandoo’s formula (i.e. the entire range is selected and then the formula entered) then it won’t work. Rather, it would return the smallest number 9 times in a row, because the ROW(B1) reference in your formula doesn’t change if the formula is entered into an entire range.

Secondly, this formula poses a problem if a new row is inserted above the range, because the ROW(B1) reference in your formula will get automatically changed to ROW(B2) – meaning the smallest number won’t get returned, and the last formula will return a #NUM error.

Probably best not to chance this, and restructure the formula slightly.

=SMALL(ROUNDDOWN($B$1:$B$9,0),ROWS(B$1:B9))

Here’s my non-array formula equivalent:

=ROUNDDOWN(SMALL($B$4:$B$12,ROWS(B$4:B4)),0)

See MrExcel podcast 1025 for an explanation of how the ROWS function works in a dynamic sorting formula. http://cdn3.libsyn.com/mrexcel/podcast1025.mp4?nvb=20090707074022&nva=20090708075022&t=0bef1acbe49d2549bbb2f

Whoops – forgot to adjust the corrected formula of yours to to reflect the ranges you use above.

Should have been like this:

“your formula above has semicolons (;) instead of commas (,) in it, so Excel won’t know what to make of it. This should be
=SMALL(ROUNDDOWN(C3:C12;0);B3:B12)”

I can’t get the posted formula to work in 2003.
But this works -
={small(rounddown(c$3:c$12,0),b2)}

Am I doing something wrong ?

Sorry, should have said b3, not b2

One more, D3 an copy down.

=SMALL(INDEX(INT($C$3:$C$12),0),ROWS($D$3:D3))

Regards

Thank you for taking the time on this. I actually am ranking this after rounding though. Eg: what place was my office compared to the other 30 after rounding the numbers. Is the basic logic the same for the formula?

@Chandoo Just to be crystal clear, you could say:

“Remember, first you should select the entire range D3:D12 and then type the formula (in the FORMULA BAR) and press ctrl + shift + enter to make it an array formula.”

@Cheryl – Either approach should work, as discussed below…but I’m not sure we’ve actually solved your problem…as discussed further below.

Firstly, array formulas can be quite a hog on your system resources, but given there are only 30 offices to rank, then it doesn’t really matter if you use an array formula or a non array formula in this case. But if you were to use a lot more array formulas than 30, then you could have performance issues, at which point you’d want to use a non-array formula like the one I posted abov.

As for whether to round first and sort last vs sort first and round last…the only time you would get a different result would be in the case where your rounding formula rounded 2 different results (say 10.23 and 10.99 rounded down to the same integer (i.e. 10). If you rank first, then round, the office with a score of 10.99 would outrank the office with a score of 10.23; but if you round first and then rank, both offices would have equal rank.

That said, I’ve just realised that the formulas we’ve posted might not really help you at all, because I dont’ think we are helping you rank a list of offices based on a result. Instead, all of our formulas round and SORT (or sort and round) an array of numbers.

While a sort is kind of like a rank, it probably doesn’t help you terribly because it’s not obvious which sorted/rounded number would apply to which particular office. That is, if your data looks something like this:
Office Result
Zurich 12.2
Paris 10.99
Sydney 10.23

Then our formulas – which round down and sort ( or sort then round down ) – would return this:
Office Result Round then Sort
Zurich 12.2 10
Paris 10.99 10
Sydney 10.23 12

…which doesn’t tell you who came first, who came 2nd , and who came third at all. Instead it just tells you that the smallest two numbers from the Result column are both 10 when rounded down, and the largest number is 12 when rounded down. Not terribly helpful if you were interested in the rankings of the offices. To find out which sorted number relates to which office would probably be tricky if not impossible for 30 different offices.

Assuming that you want to find out how each office ranks against the others, then I’m trying to understand why you want to ROUND numbers as well as RANK them. Is this because you want any 2 offices that have very close results to be ranked equally? For instance, in the case of the Paris and Sydney offices above, if rounded down to the nearest whole number and then ranked, they would receive equal rankings. Is this what you want to happen?

If so, then you can’t do this with the Round and Rank functions (as far as I know, and I’ve tried quite a few variations) but you CAN do this with the following array-entered formula:
{=SUM(1*(ROUNDDOWN(B5,0)<=ROUNDDOWN($B$5:$B$34,0)))}

Where:
• The 30 office names are in the range $A$5:$A$34
• The number you want to compare them against each other (presumably some kind of financial measure) is in the range $B$5:$B$34
• The above formula is array-entered into cell C5 (i.e. you paste the above formula minus the curly brackets into C5, then hold down Ctrl+Shift+Enter, at which point Excel adds the curly brackets) and then copied down to row C34
Let me know if this is what you need.

Ajay has a timely post on array formulas at http://www.databison.com/index.php/array-formulas-examples/

@Cheryl – If Jeff’s assumption on what you need is right, you can get it with this formula.

=SUMPRODUCT(–(INT(B2)<INT($B$2:$B$34)))+1

Regards

@Elias nice use of sumproduct. I’d use your formula rather than my array version.

Note:
I think wordpress has changed your two minus signs into a long dash. So the formula comes up wrong. It should read:
=SUMPRODUCT(- – (INT(B2)<INT($B$2:$B$34)))+1

The gap between the minus signs is just to make it clear there are 2 of them, and has no impact on performance

Thanks Jeff & Elias! Yes I want ties. I have a row where I’m indicating what the best score is and then on the row below inhave where my office ranked. Two different mgrs count differently and after 3 meetings I figured out the problem was one was quite literal and the other rounded. This was my compromise to them. I had tried many alternate formulas to try to round then rank. Glad to hear I wasn’t the only one not able to do that. I’ll test the sumproduct solution. Thanks again!

Thanks all for the great rounding / ranking function (=SUMPRODUCT(- – (INT(B2)<INT($B$2:$B$34)))+1
), it was exactly what I needed for my project. Only one question – in my case, I have a large and messy delimited spreadsheet that results in text being interspersed amidst the range. So, if I use the range as is I get the #VALUE! result. I could fix the problem just be deleting the text but it is a very large spreadsheet with many instances of the text occuring. Is there a way I can easily "adjust" the formula to ignore the text values in the range?

@Chandoo: This is terrific, thanks for providing that workaround (and so quickly – it works!). I just noticed one thing with this formula, both in the version you just provided and the original one I asked about. It seems to provide correct ranked ties in some situations (i.e. correctly associating two #1 rankings to the rounded values of 26.6 and 26.8) but not in others (i.e., when the rounded values were 14.8 and 15.3, it gave me rankings #7 and #8, when both rankings should have been #7 based on a rounded value of 15). So instead of rounding the values, the formulas seem to just “lop off” the decimal and the associated tenth value and rank based on the original integer, not the rounded value of the integer. Am I seeing this correctly?

Thanks again Chandoo, you have a great site!
Robert

@Chandoo: Thanks again, it works perfectly now! Because I can’t easily sort the rows given the type of worksheet this is, and there are almost 60 values per list being ranked, I think I’m going to use conditional formatting to 1) highlight the bottom (max) ranks which include all of the references to the blank / text cells and 2) bold any other duplicate ranks which will indicate ties, which I’m interested in. Think I have a template to use for future instances!

hi
nice posts.
i want to rank/sort data array but some values are repeating e.g.
a 7
b 4
c 7
d 4
would you please help me devising some formula that should return as under
a 7
c 7
b 4
d 4
Looking Forward