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