Cheryl asks via e-mail,
I was wondering if you could help me figure out how to combine the round formula with the rank formula? I need to first round all the numbers and then rank them. I have tried various ways to do this but they aren’t working properly. I really don’t want to add a column to round and then rank because I need to do this round/rank many times for this particular spreadsheet. There are also merged columns which makes it even more challenging. Before I go through the painstaking efforts to insert columns to round, I thought I’d check with you.
Of course, just as any other excel formula problem, we can solve this with … strong coffee.
Well, I am kidding, but coffee along with array formulas seems to help.
How to write an excel array formula to round and sort a list?
Assuming the data is in range C3:C12, and B3:B12 has running numbers from 1 to 10, the below array formula,
=SMALL(ROUNDDOWN(C3:C12,0),B3:B12)
when entered in the range D3:D12 will round and sort the range of values in C3:C12.
Remember, first you should select the entire range D3:D12 and then type the formula (in first cell) and press ctrl + shift + enter to make it an array formula.
Update: Even better alternatives from commenters
Here are few interesting alternatives, thanks to our commeneters.
- =SMALL(ROUNDDOWN($B$1:$B$9,0),ROW(B1)), array enter in cell C1 and drag in the entire range. Thanks to Chrisham.
- Instead of using ROW() as Chrisham did above, we can also use ROWS() function. This will let you add rows on top without having to change formulas. Elias recommends using this formula: =SMALL(INDEX(INT($C$3:$C$12),0),ROWS($D$3:D3)). Thank you.
- Since we are only sorting numbers, it shouldn’t matter whether we sort and round or round and sort, as Jeff pointed. So, we can use the normal formula =ROUNDDOWN(SMALL($B$3:$B$12,ROWS(B$3:B3)),0) in cell C3 and copy in the entire range. Thanks Jeff
More resources: Learn excel array formulas | Online Help on Excel Formulas
31 Responses to “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
@Chrisham: Thanks for your tip. As suggested by Jeff, we can also use ROWS()
@Jeff: Thanks for such a wonderful comment.
My response:
(1) the formula had semicolon because I was using european version of excel. I have corrected the formula now.
(2) I agree with your point. I have updated the post now.
(3) I have mistakenly thought that if you change the order of round and sort, then the results will be different. I am wrong, you are right. I have updated the post now.
Thanks for the link to Mrexcel and your formulas. They are very good.
@Gerald: hmm, that is strange, the formula should work in 2003. Did you select the entire range D3:D12 before entering the formula?
Anyways, your version also works 🙂
@Elias: Thanks for sharing your formula. 🙂
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!
@Cheryl: Sorry for misunderstanding your requirement. I thought you want to rank the values (as in rearranging them in certain order).
Let us know after trying the options suggested by Jeff and Elias.
@Jeff: that is a very good observation. After reading her mail, even I thought she wanted to rank the values. But then I later interpreted rank as common English use and sorted the values.
Thanks for the formula.
@Elias: That is very cool. Can you please explain how the formula works?
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?
@Robert: You can wrap the sumproduct with error checking using ISNUMBER formula like this: =SUMPRODUCT(--(INT(IF(ISNUMBER(B2);B2;0))
@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
@Robert... no problems, I am glad to help.
It is rounding down the values to nearest integer, so 14.8 becomes 14 and 15.3 becomes 15. If you need to round to nearest integer, replace the INT()s with ROUND()s.
@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
Hi, I was looking for some assistance with a 3 column table to be Auto Sorted and Ranked in 3 new columns.
Column B is a list of unique names
Column D is a list of scores
Column E is the ranking
I am able to get the column D to auto sort in column I with this formula =LARGE($D$2:$D$50,ROW(D1))
I can then get column E to rank easily enough with this formula =RANK(I3,$I$3:$I$33,0)
What I cannot do is get the associated name to sort in the respective order of the of the new Rank.
This is the original Data
b c d e
2 Dennis Y 39 13
3 Steve M 49 1
4 Daniel K 27 31
5 Mark F 30 26
6 Josh L 28 30
This is the sort/Rank - can't find a way (with formulae) to associate the name to the respective sort.
f g h i j
3 49 1
4 44 2
5 43 3
6 42 4
Any and all suggestions are welcome. I prefer to keep formulae as simple as possible as well. That way when I look back on it or pass it onto another to use, it is simple to understand.
@Darren... Welcome to PHD. Here is how you can sort the column "C" using formulas.
in an empty column write =index($C$2:$C$50,match(i2,$D$2:$D$50,0))
this formula is looking for the position of i2 in the list $D$2:$D$50 and then using that position to fetch corresponding element in C2:C50.
If you are interested in sorting text itself thru formulas, pls. look at : http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/
Hi, i was succesfully used the formula advise on associated name to sort in the respective order of the of the new Rank. However, when i have same score with different names, it was captured the first name only. How can i ensure both same score will capture the different name? Ex data as below :
column D column E
27 peter
26 david
26 jones
25 simon
30 lydia
Seek your advise too.
I have a slightly different problem of sorting. The data is in column A and B. Some vaules of A have multiple ocurance. I want to have a sorted and averaged list in column C and D. Is this possible with a formula?
Example raw data:
A B
2 1
3 2
3 3
1 4
What I want as output:
C D
1 4
2 1
3 2.5
Looking Forward
@Goemon
In Excel 2007/10
D1: =AVERAGEIFS($B$1:$B$4,$A$1:$A$4,C1)
Copy down
.
In Excel to 2003
D1: =SUMIF($A$1:$A$4,C1,$B$1:$B$4)/COUNTIF($A$1:$A$4,C1)
Copy down
@Hui...
thanks, thats the easy part for column D, but not the solution for a sorted list of unique values from column A in column C.
I've come up to an array formula like this in cell C2 and then copied down to the end of the list.
=IF(ISNA(OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$4)=C$1:C1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)),"",OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$4)=C$1:C1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1))
The first match in C1 is simply a
=min(A1:A4)
What do you think of this solution?
Goemon
some more searching and I found a shorter formula. So column A and B are the raw data,
in Column C I have now:
{=IF(SUMPRODUCT(1/COUNTIF($A$2:$A$400,$A$2:$A$400))>=ROWS($1:1),SMALL(IF(FREQUENCY($A$2:$A$400,$A$2:$A$400)>0,$A$2:$A$400),ROWS($1:1)),"")}
to return a sorted list of unique values from column A. And Column D holds a similar formula like your suggestion:
=IF(ISNUMBER(C2),SUMIF($A$2:$A$41,C2,$B$2:$B$41)/COUNTIF($A$2:$A$41,C2),"")
It is a little slow for long data lists, but works well enough.
Any idea how I get the standard deviation of multiple B values into column E?
i have column to sort but have same value in two cell and have another column data not same i wanna to sort
a b c d
man 2 3 3
girl 2 3 4
child 1 2 2
how to sort
sort first by b then c then d
yes you can sort Excel/Libreoffice using just formulas only.
My solution is much simpler.
1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function 😀
2. My sorted numbers shall be in cells J29-O29.
3. The formula for cell J29 is
=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))
3. The formula for cell K29 is … just convert all the “=1? into “=2?
4. The formula for the rest is “=3? for L29 and so on till “=6? for O29.
5. The RANK function will rank every cell in the range. There will not be any unranked.
6. The last part .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)
If there are more than one same number .. meaning there are more than one number of the same rank.. it would duplicated the previous number of the same rank.
7. To sort words, you need to first convert words into ASCII using the CODE function. The RANK function works only with numbers.
8. I'm sure you know how to use the ROUND function after sorting.
Hope this would help u guys.
Ariel
yes you can sort Excel/Libreoffice using just formulas only.
My solution is much simpler.
1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function 😀
2. My sorted numbers shall be in cells J29-O29.
3. The formula for cell J29 is
=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,
IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,
IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))
3. The formula for cell K29 is … just convert all the “=1? into “=2?
4. The formula for the rest is “=3? for L29 and so on till “=6? for O29.
5. The RANK function will rank every cell in the range. There will not be any unranked.
6. The last part .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)
If there are more than one same number .. meaning there are more than one number of the same rank.. it would duplicated the previous number of the same rank.
7. To sort words, you need to first convert words into ASCII using the CODE function. The RANK function works only with numbers.
Hope this would help u guys.
Ariel
Hi All,
I have a similar (but slightly different problem) to Cheryl.
I have a list of values, which I would like rounded to the nearest decimal, and then ranked (using RANK.AVE) in descending order.
A simple example of what I'm talking about:
1.714285714 (i.e. =12/7)
1.666666667 (i.e. =15/9)
I will be entering data into a range of cells as seen in the brackets, but I would like the results rounded to the nearest decimal before ranking them.
In the case above, using the RANK.AVE function, my aim is for them to both return a rank of 1.5.
Can anyone assist me with this?
Thanks,
Matt