Median Array
 

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 Array
 

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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 Array
 

On Apr 8, 12:22*pm, Max wrote:
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))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---



" wrote:
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- Hide quoted text -

- 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

Median Array
 

.. 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?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<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

Median Array
 

Matt,

Pl feedback. Tough on my senses to see yet another discussion thread
prematurely hung.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---