rank determination

**excelhelp18** · 10-30-2010, 05:23 AM

Hi,

I need your help on the following..

I have calculated the uptime% of each SiteID in respective circle. I would like to calculate the rank of each site within the individual circle based on the uptime achieved.

I have attached a sample datasheet for your reference. Columns (Circle, SiteID and Uptime) are there in my master database. I would like to build a query which will give the rank of each site id within the circles based on the uptime achieved.

Thanks in advance.

Regards,

**Marcol** · 10-30-2010, 07:33 AM

Try this in E2

=RANK(C2,INDIRECT("$C$"&MATCH(A2,$A$1:$A$20,0)&":$C$"&COUNTIF($A$1:$A$20,A2)+MATCH(A2,$A$1:$A$20,0)-1))

Hope this helps

**DonkeyOte** · 10-30-2010, 07:39 AM

@excelhelp18 - perhaps be clear - I think you want a SQL query and the Excel file reflects your Access Database, correct ?

(posting Excel files in the Access Forum generally leads to confusion)

**alansidman** · 10-30-2010, 05:26 PM

Here is the MS solution based upon their KB. There is no rank function in Access.

http://support.microsoft.com/kb/208946

Alan

**MarvinP** · 10-30-2010, 07:19 PM

Hi excelhelp18

Find the attached Pivot Table that does what you desire. The sites are alphabatised. Find the attached. Pivot tables have a Rank in show values.