Recent content by amarkbass

my apologies Faseeh - here's a public link: http://dl.dropbox.com/u/7535971/rank%20by%20region%20v4.xlsx

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...

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

also, in other regions, there are multiple states within a region number so the forumla in column H may not always work

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...

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...

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...

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...

ok, going through the tutorial now Hui

Ok will check it out, 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...

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!!

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...

thanks Hui! I'm going to try it out now!

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...