Median Array

[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

 

[Max]

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

 

[matthew.clegg]

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 -

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

 

[Max]

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

 

[Max]

Matt,

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

---