| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Rank with If
Column B contains numbers.
Column C should rank numbers in Column B, but only for sets of data matched by the Xs (X, XX, XXX, XXXX, etc.) In other words, rank all figures in Column B for X, then rank all figures in Column B for XXm, etc... Thanks for your help! Column A Column B Column C X 2 X X X X X XX XX XX XX XXX XXX XXX XXX XXX XXX XXX XXXX XXXX XXXX XXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXXX |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Rank with If
Hi!
Try this in column C: =SUMPRODUCT(--($A$2:$A$34=A2),--(B2<$B$2:$B$34))+1 Biff "SteveC" wrote in message ... Column B contains numbers. Column C should rank numbers in Column B, but only for sets of data matched by the Xs (X, XX, XXX, XXXX, etc.) In other words, rank all figures in Column B for X, then rank all figures in Column B for XXm, etc... Thanks for your help! Column A Column B Column C X 2 X X X X X XX XX XX XX XXX XXX XXX XXX XXX XXX XXX XXXX XXXX XXXX XXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXXX |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Rank with If
Assuming that A2:B10 contains the data, to rank Column B from highest to
lowest, try... C2, copied down: =SUMPRODUCT(--(A$2:A$10=A2),--(B2<B$2:B$10))+1 Hope this helps! In article , SteveC wrote: Column B contains numbers. Column C should rank numbers in Column B, but only for sets of data matched by the Xs (X, XX, XXX, XXXX, etc.) In other words, rank all figures in Column B for X, then rank all figures in Column B for XXm, etc... Thanks for your help! Column A Column B Column C X 2 X X X X X XX XX XX XX XXX XXX XXX XXX XXX XXX XXX XXXX XXXX XXXX XXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXXX |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Rank with If
Hey thanks! I get funky numbers using that. let me clarify:
a formula that in Column C that returns the same values I'm putting in below. I'm currently using the Rank Function, but I only know how to change the rank references manually for each data label. Am looking for a formula I can drag down 2000+ rows and rank the data in Column B. Col A Col B Col C Rank by Label Numbers Label X 4.0 3 X 2.8 6 X 7.3 2 X 11.7 1 X 3.0 4 X 2.8 5 XX 4.8 3 XX 1.3 4 XX 15.7 2 XX 15.7 1 XXX 10.6 2 XXX 12.1 1 XXX 1.5 4 XXX 0.9 6 XXX 1.3 5 XXX 0.5 7 XXX 1.7 3 XXXX 1.0 4 XXXX 5.6 1 XXXX 5.4 2 XXXX 1.8 3 XXXXX 6.4 1 XXXXX 3.7 3 XXXXX 5.5 2 XXXXX 2.8 4 XXXXX 1.0 5 XXXXXX 3.4 3 XXXXXX 3.8 1 XXXXXX 2.1 4 XXXXXX 1.7 6 XXXXXX 2.0 5 XXXXXX 3.8 2 XXXXXXX 3.5 1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Rank with If
Bravo! Works great. Thanks very much.
|
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Rank with If
Sorry, my mistake. Your formula works perfectly.
Bravissimo! Thanks very much! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Rank with If
You're welcome. Thanks for the feedback!
Biff "SteveC" wrote in message ... Sorry, my mistake. Your formula works perfectly. Bravissimo! Thanks very much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rank where lowest value is highest rank | Excel Worksheet Functions | |||
| RANK formula | Excel Discussion (Misc queries) | |||
| How can I rank numbers based on other numbers? | Excel Worksheet Functions | |||
| Rank fx - Fill Down? | Excel Worksheet Functions | |||
| Rank() based on category | Excel Worksheet Functions | |||
Linear Mode
