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