• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the Median Value in a sorted List

Im_Offset

Member
Hello to all you super smart Excelers,

Is it possible to get the median value of a sorted list? See the attached spreadsheet?

I have cut a pasted the values from a larger list to avoid confusion and uploading a very large file....

The top part of the spreadsheet (under the blue header) contains the data for a single, "subject" house.

The data under the green header is a list of sold houses which are all in the same market area, "4C", as the subject house (see column D).

I actually have 2 problems which I need help with:
First: I want to highlight a row Orange if a sold house is in the same Sub Market (See column C) as the subject house. I have a conditional format rule to accomplish this, and it looks to be working correctly. However, I also want to highlight a row Yellow if a sold house is in the same neighborhood (See column B) as the subject house. I have conditional format rule to do this as well, but it is not working correctly. Can someone please help me with my conditional formatting rules?

The second problem I have, has to deal with the subject of this thread: I would like to know what the median ratio is (See column T) for all sold houses that are in the same neighborhood as the subject (what should be the Yellow highlighted rows), the median ratio for all the sold houses that are in the same Sub Market as the subject (what should be all the Orange highlighted rows), and the median value for the whole list of sold houses (this is the easy part. It is the median value of the whole list). But how can I determine the median values of the other two criteria? I have cells T3 and T4 reserved for those two numbers.

Thanks!
 

Attachments

  • Chandoo example.xlsx
    58.8 KB · Views: 2
In CF you have C8 and B6 referenced. I think both should be referring to row 8.
Like so (To have yellow rows, it works like this. I guess your formulae are overlapping)
59555

For median using a condition, f.e. https://www.lifewire.com/excel-median-if-array-formula-3123300
You would need to have a helper column inserted to detect "same neighborhood" and "same Sub Market". Formulae of your CF would do. (Returning false and true).
 
Back
Top