Percentrank if function

[danlan]

Good Morning,

I'm trying to get a percentrank function recognize multiple variables in one column. Is there an array formula that can bring this formula all the way to the bottom as I'm working with a much larger data set.

In the percentrank column, I'm looking to place some type of formula that will calculate the percentile group of each store for that particular color. For example store 3 is in the 70th percentile group for red sales, but it is in the 30th percentile group for blue sales.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Color[/TD]
[TD]Sales[/TD]
[TD]Percentrank[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Red[/TD]
[TD]50[/TD]
[TD]=+PERCENTRANK($C$2:$C$6,C2,1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Red[/TD]
[TD]55[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Red[/TD]
[TD]60[/TD]
[TD].7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Red[/TD]
[TD]75[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Red[/TD]
[TD]15[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Blue[/TD]
[TD]81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[TD]68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD]48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Blue[/TD]
[TD]76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blue[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Domenic]

Try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=PERCENTRANK(IF($B$2:$B$11=B2,$C$2:$C$11),C2,1)

Hope this helps!

 

[danlan]

Thanks Domenic. That worked well. Do you have any ideas as to how to expand that to an 80,000 line file? When I used that, it completely locked up my computer.

 

[shg]

It has to do 6.4 billion comparisons, so I'd expect it to take a while.

How many stores are there among those 80000 rows? If you sorted by store and used some dynmaic ranges, it might get a lot faster.

 

[Domenic]

A couple of possibilities to avoid using PERCENTRANK as an array formula...

1) Copy each colour group to a separate sheet, and then use the regular PERCENTRANK function.

2) Sort the data by colour, in ascending order, and create a defined name to be used by PERCENTRANK. In effect, the defined name will refer to the range corresponding to the specified colour.

Post back if you prefer the second one and need help with it.

***Edit*** I see shg has already suggested the second one. I guess I'm too slow.

 

[danlan]

Thanks. I appreciate all the help. The data that I shared was a small example of what I was looking for because I can't share the actual data that I'm working with. The actual data has 25 different 'colors' and about 4,000 different stores. I didn't want to make 25 different sheets as this would be a much larger file. I think that I've done the second one in the past, and this is what I was trying to avoid if possible.

I ended up using the recommended formula, but to rather than writing the formula and dragging it to the bottom. I placed the formula into a macro that was programmed to go all the way to the bottom. This took the calculation from hours to about 10 minutes. I have no idea why that worked, but I suspected it would.

Thanks again for the help.