General Betting
There is currently 1 person viewing this thread.
10 Aug 10
12:17
Joined:
Date Joined:
10 Jan 02
The problem I have is this....
I have a spreadsheet that produces several results based on simple calculations but it is unavoidable that the results will be all jumbled up. It would simplify things for me greatly if there was a way of ranking these results from highest to lowest.
So for example..... imagine I had 50 results represented by the cells A1 to A50. Is there a formula that will rank these results from highest to lowest? (that could be represented by the cells C1 to C50) without me having to go through the results and ranking them manually?
I have a spreadsheet that produces several results based on simple calculations but it is unavoidable that the results will be all jumbled up. It would simplify things for me greatly if there was a way of ranking these results from highest to lowest.
So for example..... imagine I had 50 results represented by the cells A1 to A50. Is there a formula that will rank these results from highest to lowest? (that could be represented by the cells C1 to C50) without me having to go through the results and ranking them manually?
sort by:
Show
per page
Replies: 14
|
By:
Rank( number, array, order )
=Rank(A1,A1:A50,0) (0 for highest first, 1 for lowest first) |
|
By:
If there are no gaps in columns in the sheet, you can then sort that column into order and it'll change everything for you.
|
|
By:
cheers Lori.
|
|
By:
The one that is sometimes useful that compliments RANK is LARGE
LARGE(A1:A50,37) would return the 37th highest number. |
|
By:
*but doesn't return where it came from, just the number
|
|
By:
ta
|
|
By:
I'm probably not understanding but why not just use the sort command?
|
|
By:
Now I've actually got round to doing this I've realised that ranking the answers wasn't quite what I wanted...what I actually wanted was to rearrange them from highest to lowest. Is that possible?
|
|
By:
As Lori and Cosmic suggest use Sort.
Highlight all populated columns and then select Data, and then Sort from the drop down menu. Choose the column you want to sort by. |
|
By:
cheers Artisan...that seems to work if i just type in numbers to different cells but the list of figures I have is based on earlier calculations so the cells have cell references in rather than just the numbers which doesnt seem to work for some reason.
I should add that some of the numbers are negative (they all represent variance froma mean of zero) |
|
By:
Without understanding a bit more about the spreadsheet, and how you've set the formulae up, it's a bit hard to say. You might consider Copy, Paste Special, Values, for the earlier calculations, and then Sort on the values column. But that might stuff up how you want to use it going forward. It should be possible to sort with the calculations in place, but that does depend on how you've set the formulae up.
If you want to try and provide a bit more detail of the columns and formulae, I'll try and help. |
|
By:
I have a sheet where I input tennis players in draw order (so randomish for outright purposes) and have their outright odds next to their names.
My last column shows me just the prices from smallest to largest (no names attatched) to give me a feel for the market.... =LARGE($B$3:$B$34,32) Where B3 would be the price of the player in row 3 (the first player in the 32 player draw) B34 would be the last player and the 32 means it's the 32nd highest number (the smallest) Is that anything like what you need? |
|
By:
next line would be
=LARGE($B$3:$B$34,31) |
|
By:
thanks Artisan...that copy paste special solution did the trick.
thanks again lori |