Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using RANK function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using RANK function
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. -- Cheers, Shane Devenshire "Sanford Lefkowitz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank function | Excel Worksheet Functions | |||
Rank function | Excel Worksheet Functions | |||
Rank Function | Setting up and Configuration of Excel | |||
Rank Function | Excel Worksheet Functions | |||
Rank Function | Excel Discussion (Misc queries) |