Calculate groupings

**cmf0106** · 11-10-2009, 09:55 AM

In the file attached it lists numerous neighborhoods and their price range. I would like excel to automatically figure out which price range group a neighborhood falls in.

Using the following price range breaks 0-139$ 140-174$ 175-224$ 225-274$ 275-324$ 325-374$ 375-424$ 425$+

Again, I would like excel to automatically look at the min-max price for the neighborhood and determine which of the price range breaks the neighborhood falls under.

For example hypothetical neighborhood A has a price range of 129-134$, that would categorize it within the 0-139$ break as stated above, I would like excel to then automatically write the price break group it belongs too for that neighborhood in the "price range" column.

**SamuelT** · 11-10-2009, 10:17 AM

Hi there,

See attached. Made a table to use a VLOOKUP and the TRUE for the Range Lookup. I have the formula looking at the higher price - however this can easily be changed to the lower one by altering the formula thus:

Please Login or Register  to view this content.

Change to...

Please Login or Register  to view this content.

HTH,

SamuelT

**martindwilson** · 11-10-2009, 11:20 AM

what price range does
208 260 fall in?
175-224$ or 225-274$ ?

**cmf0106** · 11-10-2009, 12:34 PM

Originally Posted by martindwilson

what price range does
208 260 fall in?
175-224$ or 225-274$ ?

Yes I was looking at that too, Im not sure how I should go about classifying ones that qualify for two or more price categories.

**martindwilson** · 11-10-2009, 12:55 PM

=LOOKUP(F2,{0,140,175,225,275,325,375,425},{"0-139$","140-174$","175-224$","225-274$","275-324$","325-374$","375-424$","425$+"}) for lower

**cmf0106** · 11-10-2009, 02:27 PM

Originally Posted by martindwilson

what price range does
208 260 fall in?
175-224$ or 225-274$ ?

Here is what I know, the sections are broken up by a price range. The user is querying the data to custom price ranges, and since some of the neighborhood price ranges can double dip it has to calculate what percent of the closings go into one price bucket or another.

For example, In section 1 the price range is 200-270$, but this falls into TWO user defined price segments 200-249$ and 250-299$. Of the 17 closings in section1 12.1 out of 17 (71%) fell in the custom price range of 200-249, while 4.9/17 (28%) fell in the other user defined price range of 250-299$. Beyond this I am very confused. If one of you bright individuals would please help explain it would be greatly appreciated.

Specifically I am having difficulty with the calculation in step3.

Here is the formula they are using.
http://img.photobucket.com/albums/v1...thodology2.jpg Or alternatively check the attachment to this post.