using RANK function

[Sanford Lefkowitz]

I have a list like
product customer revenue revenue
year 1 year 2 .....
A cust1 100 110
A cust2 120 65
A cust 3 139 88
...
B cust 1 333 500
B cust 2 122 220
B cust 3 65 287
...
for several years, products and customers
What I want to do is get each customer's rank within each product for each
year.
I will be using this each month and the number of customers within each
product class will change from month to month. So, I cannot use somthing like
RANK(A23,A15:A90)
because the starting and ending rows for a product will change each month.
How can I set up the RANK function to pick up the staring and ending points
of each product?

Thanks
Sanford

 

[ShaneDevenshire]

Hi Sanford,

Here is one solution:

Suppose columns G, H, and I are available. Suppose your data starts on row
2 with titles on row 1. With your categories in column A and the values you
want to rank in column B:

In G2 enter the formula: =IF(A2=A1,G1,ROW(A2))
In H2 enter the formula: =G2+COUNTIF(A$2:A$100,A2)-1
In I2 enter the formula: =RANK(B2,INDIRECT("B"&G2&":B"&H2))

In this example I have assumed the data could go down to row 100, but you
can adjust that reference.