I need to rank groups of prices within a column separately which is easy enough except I don't know in advance how many prices are going to be in each group. ie the formula needs to be able to refer to a separate array which identifies the subsets within the column. I have got it working for calculating max, min, sum etc but I can't get the rank function to work.
Eg the following calculates the smallest price within each of the groups. GR is the column with the prices and GW is the array that gives each subset of prices in that column a unique id. I copy this down the sheet and it picks out the smallest price for each of the subsets
=MIN(INDIRECT("$gr$"&MAX($GW$2:$GW2)&":$gr"&ROW())))
I am lost trying to create a formula to rank the prices within each subset. I was guessing something along the lines of
=RANK(INDIRECT("$gr$"&MAX($GW$2:$GW2)&":$gr"&ROW())))
but that came up with the error too few arguments. (I also need to add the 1 to make it sort ascending)
Any ideas would be really appreciated as I find arrays very difficult and can't see how to fix this one
Cheers
Rhys
Bookmarks