• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to group products by region then rank -- possibly using named ranges?

A

amarkbass

New Member
I'm stumped! I'm trying to rank counties based on the estimated number of paint cans and shoes (columns F, G and H) and the following critieria:


1st 5 = rank A

6 - 10 = rank B

all else = rank D


In order to be ranked as an A or B, the minimum volume must be equal to one or greater.


I created this formula using nested if/and/or/large functions -- =IF(OR(AND(F2>0.99,F2=LARGE($F$2:$F$15,1)),AND(F2>0.99,F2=LARGE($F$2:$F$15,2)),AND(F2>0.99,F2=LARGE($F$2:$F$15,3)),AND(F2>0.99,F2=LARGE($F$2:$F$15,4)),AND(F2>0.99,F2=LARGE($F$2:$F$15,5))),"A",IF(OR(AND(F2>0.99,F2=LARGE($F$2:$F$15,6)),AND(F2>0.99,F2=LARGE($F$2:$F$15,7)),AND(F2>0.99,F2=LARGE($F$2:$F$15,8)),AND(F2>0.99,F2=LARGE($F$2:$F$15,9)),AND(F2>0.99,F2=LARGE($F$2:$F$15,10))),"B","D"))


but the locked ranges makes things very difficult when copying and pasting to other cells, this needs to be done for 80+ regions


my hope is that someone would be able to help me avoid having to lock the ranges, maybe using named ranges somehow for the region #.


Ideally, I would like to be able to eliminate the two black rows (originally created for sorting each table and manually plugging in A, B or D prior to creating the formula) and have the workbook only consider the ranking based on common region #.


FYI there are a different number of counties in each region.


The purpose of the ranking by class A and B would be to copy and paste all of the A states and counties onto a seperate tab, B's on another to upload into another application. So the A's and B's might not even be needed, as long as the appropriate counties/states end up on the correct upload tab.


ANY help at all would be greatly appreciated!


link to file: https://www.dropbox.com/s/x84mgvwo01xglc4/rank%20by%20region.xlsx
 
Your current formula can be replaced with

I2: =IF(F2>LARGE(F$2:F$15,6),"A",IF(F2>LARGE(F$2:F$15,11),"B","D"))

Copy down/across


I'm thinking of a more scaleable solution but have to got out for a while
 
Back...


In I2:

=IF(F2>LARGE(IF(D$2:D$50=D2,F$2:F$50,),5),"A",IF(F2>LARGE(IF(D$2:D$50=D2,F$2:F$50,),11),"B","D")) Ctrl Shift Enter


Then copy I2 down and across
 
Ok so I adjusted the formula and it seems to work 90% of the time --


in I2: =IF(F2>LARGE(IF($A$2:$A$50=$A2,F$2:F$50,),6),"A",IF(F2>LARGE(IF($A$2:$A$50=$A2,F$2:F$50,),11),"B","D"))


I moved both large ranges from D to A and made them fully absolute and upped the large position for the first group to 6.


The only issue seems to be the requirement that the minimum volume must be greater than or equal to one -- cells J40:J44 should be D's, same with I26 and I27.


link to updated file: https://www.dropbox.com/s/xs3yi80heuirmiy/rank%20by%20region%20v2.xlsx


Amazing formula Hui, I've used CSE/array formulas before but it seems like I've got to increase my knowledge base in this area.
 
I think I've got it... looks like the fix is:


=IF(AND(F2>LARGE(IF($A$2:$A$50=$A2,F$2:F$50,),6),B2>0.99),"A",IF(AND(F2>LARGE(IF($A$2:$A$50=$A2,F$2:F$50,),11),B2>0.99),"B","D"))


will check back... Thanks again Hui!!
 
It works, Hui, you're the man!


Any suggestions on how to learn more about array formuals? links to websites other than microsoft's? examples?


I can create another thread for this topic if necessary...
 
Glad you worked it out


Although not teaching you how to think about Array Formulas the Formula Forensics series has about 30 worked examples of how to use them

http://chandoo.org/wp/category/formula-forensics/
 
Also have a look at: http://www.xtremevbtalk.com/showthread.php?t=296012
 
Ok so I've been scouring the internet (including Hui's helpful links on arrays) trying to find a way to do nearly the same thing as before except this time using the percentile function


This time around, there is no maximum number of counties per region number, the paint can classes are now from 1 to 5 (rather than A, B and C) and are based on .2 percentile increments.


Column G shows what I'm looking for but I'd need to perform the same excercise for each region (limited the range in column G to the first region's rows).


My attempt at it is found in col H... what could I be missing? Is the CSE/array function even necessary?


link to file: https://www.dropbox.com/s/ywj3woxo9kstmu5/rank%20by%20region%20v3.xlsx?m


ANY help would be greatly appreciated!!
 
I'm trying another method, ranking the est # of paint cans by region and then calculating the percentile based on the rank. I dumped this formula in column J and dragged down:


=SUMPRODUCT(($A$2:$A$46=A2)*($F$2:$F$46>F2))+1


which gives me the rank per region, which is a minor victory (boom!) however I'm still stumped on how to calculate the percentile per region -- either using this new method or the original via arrays
 
Allllright, so with the help of a friend, I developed an extrememly manual process using percentile/if statement array table. After sloggin through it all weekend, I was able to knock it out, however I have to adjust the range for each region.


Is there anybody out there that can help me come up with a process that is purely drag and drop that breaks up the number of prospects (col E) into groups of 5 or quintiles, by region number (col A).


file: https://www.dropbox.com/s/ncbe8o0cdnyddsv/rank%20by%20region%20v4.xlsx?m


Should I start a new thread?
 
Hi amarkbass,


Can you see this file:


http://dl.dropbox.com/u/60644346/rank%20by%20region%20v3%20_%20Faseeh.xlsx


Regards,
 
Faseeh kind sir! this might actually work! My only question would be, does it matter that the bottom or end of the ranges listed in column K are one cell too long?


For example, in cell K15: ="F"&SMALL(IF(ISNUMBER($I$2:$I$46)=TRUE,ROW($I$2:$I$46)),J15)&":F"&IFERROR(SMALL(IF(ISNUMBER($I$2:$I$46)=TRUE,ROW($I$2:$I$46)),J15+1),COUNTA($I$2:$I$46)+1)


but returns a value of F2:F16 when the true range for this region number is F2:F15
 
also, in other regions, there are multiple states within a region number so the forumla in column H may not always work
 
Hi amarkbass,


Regarding ur first question of adjusting ranges, it is possible to adjust it. And for ur second question of multiple regions and multiple states, i request u to upload a comprehensive sample file. Its 1.00 am here so see u tomorow.


Faseeh
 
For ur first query it can be adjusted. Regarding ssecond plz upload a complete sampple file.
 
Faseeh - Thanks for your help. Here's an updated file using your method:


https://www.dropbox.com/s/3nsycjvtq21a9as/rank%20by%20region%20v5.xlsx?n=7535971
 
Hi amarkbass,


You can use following formula for your first query place in j2 and drag down:


="F"&SMALL(IF(ISNUMBER($H$2:$H$52)=TRUE,ROW($H$2:$H$52)),I2)&":F"&IFERROR(SMALL(IF(ISNUMBER($H$2:$H$52)=TRUE,ROW($H$2:$H$52)),I2+1)-1,COUNTA($H$2:$H$52)+1)


enter as CSE formula.


For your regions, can you elaborate how will you calculate percentile.
 
Faseeh, that is exactly what I needed! Thank you!


To answer your quesiton, I believe that you already understand how I'm using percentile. I'm using it to group each prospect class or quintile into similarly sized groups. For more clarity on how I calculated percentile, take a look at this file:


https://www.dropbox.com/s/ncbe8o0cdnyddsv/rank%20by%20region%20v4.xlsx?m


As you can see, the process I used is VERY manual. I had to adjust the range for each region. I was using the percentile function in columns M through Q as a way to divide prospects in column E evenly. Column F says, if you're below the lowest percentile, return a 1, and so on..


One difference between your process and mine is that I excluded 0's in order to keep counties with 0 prospects from dragging the bottom of the range down:

=PERCENTILE(IF($E$2:$E$15<>0,$E$2:$E$15),M$1)


You have defintely pointed me in the right direction..


God bless you! And thanks again!
 
Hi amarkbass,


You are welcome, but he last link you have given is not working. Please check.


Faseeh
 
Hi amarkbass,


Enter this formula in K2 & drag down:


=G2+ROW()/10000


Enter this formula in L2 & drag down:


=INDEX($D$2:$D$52,MATCH(SMALL($K$2:$K$52,ROW(A1)),$K$2:$K$52,0),0)


Regards,
 
You must log in or register to reply here.
Back
Top