Median Array

  • Thread starter Thread starter matthew.clegg
  • Start date Start date
M

matthew.clegg

I've got an array function working that filters data by a certain
specified date (Dates!A1) and then returns the median for that fitered
subset of data.
See below:
={MEDIAN(IF(Data!$A$1:$A$200=Dates!A1,Data!$B$1:$B$200,""))}

I would like to extend it so that, rather than calucalting the median
for all data that equals a particular date, it would calculate the
median for all data that does not equal a number of dates. For
instance calculate the median for all data in column B where all the
dates in column A do not equal bank holidays as defined in Dates!
column A.

Cany anyone suggest how to do this?
Cheers
Matt
 
median for all data that does not equal a number of dates.

Create a defined range: BankHol
to refer to the specific list of dates to be excluded (a contiguous range)
eg: BankHol =Dates!$X$1:$X$10

Then use this expression, array-entered:
=MEDIAN(IF((ISERROR(MATCH(Data!$A$1:$A$200,BankHol,0))*(Data!$A$1:$A$200<>"")),Data!$B$1:$B$200))
 
Create a defined range: BankHol
to refer to the specific list of dates to be excluded (a contiguous range)
eg: BankHol =Dates!$X$1:$X$10

Then use this expression, array-entered:
=MEDIAN(IF((ISERROR(MATCH(Data!$A$1:$A$200,BankHol,0))*(Data!$A$1:$A$200<>"­")),Data!$B$1:$B$200))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---







- Show quoted text -
Click to expand...

I array entered the following
=MEDIAN(IF((ISERROR(MATCH($D$3:$D$134,BankHol,0))*($D$3:$D$134<>"­")),
$E$3:$E$134))
Column D= dates
Column E= data
BankHol= named range of dates to be excluded from median calc

This didn't however give the correct result?
Cheers
Matt
 
.. This didn't however give the correct result?

well, I got the correct results as illustrated in my test/sample,
link to it here for your easy reference:
http://www.freefilehosting.net/download/3f127
Median Excluding Certain Dates.xls
(I actually framed it up to closely suit what you indicated in your original
post)

Maybe try checking at your end:
- is the expression correctly array-entered? (see the curlies { } in the
formula bar)
- are the values in your col D all real dates?
- are the values in your BankHol defined range all real dates?

---
<matthew.... > wrote
I array entered the following
=MEDIAN(IF((ISERROR(MATCH($D$3:$D$134,BankHol,0))*($D$3:$D$134<>"­")),
$E$3:$E$134))
Column D= dates
Column E= data
BankHol= named range of dates to be excluded from median calc

This didn't however give the correct result?
Cheers
Matt
 
Matt,

Pl feedback. Tough on my senses to see yet another discussion thread
prematurely hung.

---
 
Back
Top