Help : Median with IF & OR

[_Scott_]

Hi Guys

Maybe I am approaching this the wrong way or just been staring at the screen
way too long but...

I have a worksheet (Full Data) with a list of property information such as,
Suburb (Col B), Type (Col M), Date sold (Col J), Price (Col I)

Say the Suburbs names are U , V, W , X , Y & Z, I want to get the MEDIAN
of W,Y & Z suburbs, but only if they fall between particular date, and are a
particular type of property.

Here is what I have tried so far making it an array formula

=MEDIAN(IF(OR('Full Data'!$B$1:$B$9999=Calculations!$B$3,'Full
Data'!$B$1:$B$9999=Calculations!$B$4),IF('Full
Data'!$M$1:$M$9999=Calculations!$A$478,IF('Full
Data'!$J$1:$J$9999>Calculations!B$477,IF('Full
Data'!$J$1:$J$9999<Calculations!B$478,'Full Data'!$I$1:$I$9999)))))


Any help will be greatly appreciated

cheers
Scott

[Biff]

Hi!

Try this:

Suburbs

A2 = W
A3 = Y
A3 = Z

Type

A7 = "whatever"

Date range

A10 = start date = 1/1/2005
A11 = end date = 1/31/2005

Array entered:

=MEDIAN(IF((ISNUMBER(MATCH(B2:B10,A2:A4,0)))*(M2:M10=A7)*(J2:J10>=A10)*(J2:J10<=A11),I2:I10))

Biff

"_Scott_" <http://efi5l@NOSPAM.westnet.net.au> wrote in message
news:43bc6b45$0$1047$61c65585@un-2park-reader-01.sydney.pipenetworks.com.au...
> Hi Guys
>
> Maybe I am approaching this the wrong way or just been staring at the
> screen way too long but...
>
> I have a worksheet (Full Data) with a list of property information such
> as, Suburb (Col B), Type (Col M), Date sold (Col J), Price (Col I)
>
> Say the Suburbs names are U , V, W , X , Y & Z, I want to get the MEDIAN
> of W,Y & Z suburbs, but only if they fall between particular date, and are
> a particular type of property.
>
> Here is what I have tried so far making it an array formula
>
> =MEDIAN(IF(OR('Full Data'!$B$1:$B$9999=Calculations!$B$3,'Full
> Data'!$B$1:$B$9999=Calculations!$B$4),IF('Full
> Data'!$M$1:$M$9999=Calculations!$A$478,IF('Full
> Data'!$J$1:$J$9999>Calculations!B$477,IF('Full
> Data'!$J$1:$J$9999<Calculations!B$478,'Full Data'!$I$1:$I$9999)))))
>
>
> Any help will be greatly appreciated
>
> cheers
> Scott
>