Search results

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

problem solved! was able to pop the sheet name in there... before: ROW(1:1) after: ROW('Sheet Name'!1:1)

or instead of the row(1:1) thing, use named ranges?

by named ranges, you're referring to the name manager in the formula ribbon right?

In a previous post someone referenced this technique as a fix: http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx what if, you needed to search one source and return values accross multiple tabs/sheets...

got it. Thanks Luke M

what if, using the example listed in microsoft office website, you needed to search one source and return values accross multiple tabs/sheets? ie: one tab for Ashsish, another for Sanjay and so on? The row function that uses a colon ROW(1:1) will not work when referencing another tab... or...

Luke M - I'm sure you've heard this many times before, but you are the MAN/a genius! Thanks a ton! I hope to be able to answer an excel question for someone in the future just as you've helped me... AMB

Incredible Luke M! I'm currently using a CSE/array formula for another sheet using a combination of index, small, if, row to give me a kind of 'continuous vlookup' deal... I'll try this one out and let you know how it goes. Even if it doesn't work, I'd love to find out more about this MOD...

Thank you for the responses fred and Hui. If you'll notice, the values listed in the first subtotal calculation are not a range of numbers (ie: =SUBTOTAL(1,L29:GF29). Each category is seperated by about 7 columns. Each subproduct includes the same 7 column headings: volume, cost, price...

Hello, I've gotten a lot of great info on this site, I figured that I'd ask my question here before anywhere else --- this is my first post. I'm trying to reduce the amount of manual selecting/deselecting of cells somehow. Any help would be greatly appreciated. I have a range of products and...