How to create rank string based on 'category' + 'price' column?

**amateurmetheus** · 05-18-2010, 01:53 PM

Greetings!

I'm trying to rank data and assign a value in the "Rank" column. No matter how many combinations I seem to try, it doesn't work. The obvious answer is "sort the data first", but that isn't practical for this scenario, as the end-users aren't tech-savvy in the least bit...they need to be able to paste in the values and *poof* it's done.

Basically, I have three real estate property categories in my spreadsheet: Active, Sold, and Unsold. Active is all currently-listed properties. Sold is all sold properties. Unsold is anything that was listed and either expired or was withdrawn.

what I'm looking to do is create something similar to this (sorry for the make-shift columns)...the "Rank" column is the is the calculated value:

Rank Status Price
Sold 2 Sold 203000
Unsold 4 Unsold 270000
Sold 4 Sold 206000
Active 1 Active 219900
Active 2 Active 219900
Sold 3 Sold 205000
Active 3 Active 224900
Unsold 2 Unsold 244900
Unsold 3 Unsold 269500
Active 4 Active 225900
Unsold 1 Unsold 233500
Sold 1 Sold 203000

The "Rank" column should look at the "Status" and "price" column; the lowest-priced value for each status group should be "1", second-lowest is "2", and so-forth. In the event of two properties in the same category having the same price, it should assign the first occurance the lower value...(see Active 1 and Active 2 in data above...both are 219900.)

Once the formula determines that numerical value, then adds the "Status" from the next column over to create a unique rank value...ie, cell A2 is "Sold 2"... formula would be =B2&" "&[rank formula here]

Could somebody please help me out on this? It's one of the last items on my list on this project before I can be done with it.

Thank you in advance!

:::a