I have a list of properties with dates of purchase and sold, i need to find the median of a range subset with it that have an sold value equal to S. I cannot see to get the date range and sold value S in the subset so I can calculate the median, this is what I have but I dont think it is right.
=MEDIAN(IF(Sheet1!$H:$H>=F165,Sheet1!$G:$G))
where H is the date column, G is the price, I have F column for the sold value, not sue how to put it all together.
Post up a sheet with good sample data and perhaps focus on a range of that data and show what your expected answer is so we're sure to get it right.
Click GO ADVANCED then use the paperclip icon to attach a worksheet.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Based on your narrative I suspect:
=MEDIAN(IF((Sheet1!$H$1:$H$1000>=F165)*(Sheet1!$F$1:$F$1000="S"),Sheet1!$G$1:$G$1000))
committed with CTRL + SHIFT + ENTER
Although you are running XL2007 and can thus have entire column references in Array (eg H:H) you should not do this as performance will be affected -- reduce the range sizes to a minimum - eg in the ex. set to 1000 rows alter as required but don't use H:H....
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
S 729,000 15-Jan-09 707,000 23-Mar-09
S 325,000 1-Jan-09 315,000 5-Mar-09
S 684,900 31-Oct-08 642,000 26-Dec-08
S 247,900 20-Oct-08 219,000 30-Jan-09
S 324,900 14-Oct-08 320,000 24-Dec-08
S 199,900 10-Oct-08 200,000 3-Nov-08
S 132,900 2-Oct-08 135,600 26-Nov-08
S 640,500 2-Oct-08 535,000 4-Mar-09
S 145,900 16-Sep-08 140,000 13-Oct-08
what I want is the median value of the records that are between 3-nov-08 and 30-dec-08 and first column is S and return the median value of the 4column, price. So I need to have and botton and top date range to collect the subset for median. Did that make sense.
and there could be blanks as well :>
=MEDIAN(IF((Sheet1!$D$1:$D$1000>=DATE(7,F3,F4))*(Sheet1!$D$1:$D$1000="S")*(Sheet1!$D$1:$D$1000<>""),,),Sheet1!$G$1:$G$1000) and this does not return dynamic values, always the same value
The above makes little sense and bears little correlation to that you were provided with (which was based on the ranges you specified earlier)... you're referencing the same column for dates & S ... you're using 7 as the year (ie 1907) in your DATE value... I have no idea what the ,, relate to... do you need to check for blank given you're looking for S -- would you ever have S and a blank date, I would presume not.Originally Posted by cstalker
Post a sample file.
im sorry, I have been trying a few different combinations and I mistakenly uploaded the wrong formula. Here is an sample data file,
here is the criteria
median for this unsort list that lies below some date and above some date and column D = "S" and the median of that result.
I still have little idea what your expected results are and or which columns you intend to use ... dates & values each appear in multiple columns so which are you using ?
Please revise the attachment so the data is clearly labelled, the expected result(s) clearly identifiable such that we can attempt to establish the logic used.
column H is the date to be evaluated against, it could have a blank.
column D is of type S
column G is the value for median return
no other columns are used.
here is a revisied spreadsheet
A few points... you say you're evaluating date based on H yet your expected results are clearly filtered by date in F... and also missing row 6 which falls within 1-Oct-08 to 1-Nov-08 parameters...
In terms of calculating the Median it would be:
=MEDIAN(IF(($D$2:$D$18="S")*($F$2:$F$18>=DATE(2008,10,1))*($F$2:$F$18<=DATE(2008,11,1)),$G$2:$G$18))
committed with CTRL + SHIFT + ENTER
Obviously the references to Dates can be altered to be cell references containing criteria values.
For actually filtering the data - I would advise you apply a standard Data Filter across A:L ... you can then filter the data as you wish (ie based on the above formula you would apply a filter to D & F).. for more info see: http://www.contextures.com/xlautofilter01.html
ok, can you do me one favor and put it in the spreadsheet and re-post because when I try to do it I get #value error in the formula feild
see the last field in this spreadsheet
see the text in bold in the prior post... note also your prior manual calc did not use H column as date criteria so your result will be very different.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks