Using dynamic ranges to Grade..

[all4excel]

1st Parameter 2nd Parameter 3rd Parameter
Sr.No. Store # Population Households Median HH Income

The above are column names in the DATA tab, Now there are different tabs viz. Population Households Median HH Income

The random under Population gets programmatically sorted and gets Graded by dividing the total number of records by 5, if the total is not completely divisble by 5 then the excess portion goes in the last grade "E".

With Daddylonglegs help I am able to get the first vakue in each range..

I want these values to be used while Grading the data for New Stores Data..

For ex - in Population the

A B C D E
44 44 44 44 48
443111 256815 141346 74330 19981
If the value in the New Data falls above 19981 - 74330, then it gets the Grade "D".

So I want these ranges to get picked while grading the New Stores Data in New Data Grid. when we the formula Left to Right....

Attaching the file...

please provide explanation wherever neccessary...

Thanks in anticipation.

[daddylonglegs]

Are the grades you show in New Data Grid worksheet what you expect to get? For instance you show a D in cell C3. Store #3 in New Stores Data has a population of 422000, should this make it a B grade?

It's going to make it much easier if you can list the values in Population!G3:K3 in reverse order, i.e. lowest to highest. That would allow a LOOKUP formula to be used, is that possible?

[all4excel]

Are the grades you show in New Data Grid worksheet what you expect to get? For instance you show a D in cell C3. Store #3 in New Stores Data has a population of 422000, should this make it a B grade?

It's going to make it much easier if you can list the values in Population!G3:K3 in reverse order, i.e. lowest to highest. That would allow a LOOKUP formula to be used, is that possible?

--------------------------------------------------------------------------

Thats possible to have the values in Population!G3:K3 in reverse order, i.e. lowest to highest however for all practical purposes it helps in displaying the best on Top to Bottom approach.

...If its not too much to ask for, can u please work both ways...?

This is the only missing portion remaining in the grid to deduce the best locations to open new stores...

[daddylonglegs]

Try this formula in New Data Grid C3 copied across and down to D8

=INDEX(INDIRECT("'"&C$1&"'!$G$1:$K$1"),MIN(5, COUNTIF(INDIRECT("'"&C$1&"'!$G$3:$K$3"),">="& VLOOKUP($A3,'New Stores Data'!$A$3:$F$8,MATCH(C$1,'New Stores Data'!$A$2:$F$2,0),0))+1))

[all4excel]

Try this formula in New Data Grid C3 copied across and down to D8

=INDEX(INDIRECT("'"&C$1&"'!$G$1:$K$1"),MIN(5, COUNTIF(INDIRECT("'"&C$1&"'!$G$3:$K$3"),">="& VLOOKUP($A3,'New Stores Data'!$A$3:$F$8,MATCH(C$1,'New Stores Data'!$A$2:$F$2,0),0))+1))

I had replied to this mail earlier but due to some tech issues with the site that never got posted...

Anyways Im sorry for replying a little late..

I think u have done everything for me on this thread..can u please provide the explanation..

Is it possible to also avoid reserving the data in each tab for storing the ranges...and instead use it programmatically and not have it stored in G:K and then use it in the grid directly...

Excellent excellent just excellent..

Please reply to the mail....

Please reply and provide an explanation as I am learning lots from your code..