| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank non Contiguous
Hi
Is there any way to have non contiguous range in Rank Currently using =RANK(D6,$D$6:$D$110)+(COUNTIF($D$6:D6,D6)-1) and works well, but do need to remove D93 and D94 from the range. Tried removing the formula from the cells, however, got error, when using vlookup also tried =RANK(D6,$D$6:$D$92,$D$95:$D$110)+(COUNTIF($D$6:D6 ,D6)-1), but did not work Help appriciated Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201002/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rank non Contiguous
Maybe if you deduct this from the total
SUMPRODUCT(--(D93:D94D6)) I haven't tied that in with your Countif part, so better check that aspect Regards, Peter T "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:a36ae60bcd20a@uwe... Hi Is there any way to have non contiguous range in Rank Currently using =RANK(D6,$D$6:$D$110)+(COUNTIF($D$6:D6,D6)-1) and works well, but do need to remove D93 and D94 from the range. Tried removing the formula from the cells, however, got error, when using vlookup also tried =RANK(D6,$D$6:$D$92,$D$95:$D$110)+(COUNTIF($D$6:D6 ,D6)-1), but did not work Help appriciated Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201002/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rank non Contiguous
I haven't tried it, but I think I'd try to create a non-contiguous named
range and put that in your formula. -- HTH, Barb Reinhardt "BNT1 via OfficeKB.com" wrote: Hi Is there any way to have non contiguous range in Rank Currently using =RANK(D6,$D$6:$D$110)+(COUNTIF($D$6:D6,D6)-1) and works well, but do need to remove D93 and D94 from the range. Tried removing the formula from the cells, however, got error, when using vlookup also tried =RANK(D6,$D$6:$D$92,$D$95:$D$110)+(COUNTIF($D$6:D6 ,D6)-1), but did not work Help appriciated Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201002/1 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rank non Contiguous
thank you both for your response
I did try the comma to separate the ranges, but could not get it to work. The non contiguous named range worked a treat regards Barb Reinhardt wrote: I haven't tried it, but I think I'd try to create a non-contiguous named range and put that in your formula. Hi [quoted text clipped - 10 lines] Brian -- Message posted via http://www.officekb.com |
| 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 | |||
| Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column | Excel Programming | |||
| RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
| Rank where lowest value is highest rank | Excel Worksheet Functions | |||
| Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Linear Mode
