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 |
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 |
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 . |
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 |
All times are GMT +1. The time now is 12:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com