| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
RANK not resulting as hoped
I guess I misunderstand how RANK works...
The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
=IF(C10=0,"",(COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF(C$10:$IV10,C10)-1)+1))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
Try this:
=IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
To: Bob & T.
Thank you very much! Both do the trick. I'm not sure which is better but I like that T.,s is shorter so I'm going with that. Thanks again, Jeff "T. Valko" wrote: Try this: =IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Jeff" wrote in message ... To: Bob & T. Thank you very much! Both do the trick. I'm not sure which is better but I like that T.,s is shorter so I'm going with that. Thanks again, Jeff "T. Valko" wrote: Try this: =IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
Sorry T., I spoke too soon€¦
I found that Bob's is actually working better. Both worked in that particular row but I found that using yours in rows were I have many blanks in between, it gave me the same problem I originally had but Bob's does work in that case. Thanks again for all your help. Jeff "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Jeff" wrote in message ... To: Bob & T. Thank you very much! Both do the trick. I'm not sure which is better but I like that T.,s is shorter so I'm going with that. Thanks again, Jeff "T. Valko" wrote: Try this: =IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
One more thing...
I also found that this problem seems to only happen when the range is horizontal. Strange as it is, even my original formula doesnt have this problem with vertical ranges. Jeff "Jeff" wrote: Sorry T., I spoke too soon€¦ I found that Bob's is actually working better. Both worked in that particular row but I found that using yours in rows were I have many blanks in between, it gave me the same problem I originally had but Bob's does work in that case. Thanks again for all your help. Jeff "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Jeff" wrote in message ... To: Bob & T. Thank you very much! Both do the trick. I'm not sure which is better but I like that T.,s is shorter so I'm going with that. Thanks again, Jeff "T. Valko" wrote: Try this: =IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
Hmmm...
I tested both formulas extensively and they both return identical results no matter how I "abuse" them! -- Biff Microsoft Excel MVP "Jeff" wrote in message ... Sorry T., I spoke too soon. I found that Bob's is actually working better. Both worked in that particular row but I found that using yours in rows were I have many blanks in between, it gave me the same problem I originally had but Bob's does work in that case. Thanks again for all your help. Jeff "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Jeff" wrote in message ... To: Bob & T. Thank you very much! Both do the trick. I'm not sure which is better but I like that T.,s is shorter so I'm going with that. Thanks again, Jeff "T. Valko" wrote: Try this: =IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
RANK not resulting as hoped
Sorry again T.,
You are absolutly correct. I must have modified your code wrong or something the first time I tried it. I tried it again and it works perfectly. Thanks again, Jeff "T. Valko" wrote: Hmmm... I tested both formulas extensively and they both return identical results no matter how I "abuse" them! -- Biff Microsoft Excel MVP "Jeff" wrote in message ... Sorry T., I spoke too soon. I found that Bob's is actually working better. Both worked in that particular row but I found that using yours in rows were I have many blanks in between, it gave me the same problem I originally had but Bob's does work in that case. Thanks again for all your help. Jeff "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Jeff" wrote in message ... To: Bob & T. Thank you very much! Both do the trick. I'm not sure which is better but I like that T.,s is shorter so I'm going with that. Thanks again, Jeff "T. Valko" wrote: Try this: =IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$1 0:C10,C10)-1) -- Biff Microsoft Excel MVP "Jeff" wrote in message ... I guess I misunderstand how RANK works... The following is pasted across row C11:IV10 "=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)" Value in BO10 and CD10 are the same (201) which is the highest in that row... but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other way around. In other words, rank duplicate values higher the further (to the right) across the row they are. Thank you, Jeff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
| RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
| Rank where lowest value is highest rank | Excel Worksheet Functions | |||
| Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. | Excel Worksheet Functions | |||
| Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Linear Mode
