median if

[Irv1010]

I am trying to get the median of a column depending on a further 2 columns
and I have been trying the following:

=MEDIAN(IF('Incl 0 LOS ''09'!O4:O13427="SJH8",IF('Incl 0 LOS
''09'!P413427="May",'Incl 0 LOS ''09'!E4:E13427)))

There are different numbers in column O, SJH1, SJH2 etc. Column P is a date
formatted as mmmm and Column E is random digits ranging from 0-49. All I get
is a return of 0 when I try and enter it. If I try and Ctrl,Shift & Return I
get NUM error. Please help.

 

[chg]

Try this:

IF(Month(*where you wanna look*)=5, etc)

Did you by any chance want to do the formula like this: =if(and
(condition 1, conditon 2),do this if true,do this if false)

 

[Bernie Deitrick]

Try finding the month #

=MEDIAN(IF('Incl 0 LOS ''09'!O4:O13427="SJH8",IF(MONTH('Incl 0 LOS ''09'!P413427)=5,'Incl 0 LOS
''09'!E4:E13427)))

HTH,
Bernie
MS Excel MVP

 

[Irv1010]

Very sorry but I'm still getting results of 0. If Iuse Array I get a #Ref
Error message

 

[Bernie Deitrick]

Irv,

You must have equal size ranges (same number of rows) and none of the comparisons can return errors.
Make sure that the line wrapping didn't include an extra space in your sheet name. Try renaming the
sheet - if the sheet names in the formula don't update, that is your error. Also, try the formula
without a sheet name, on a truncated range where you can control the entry easily, say

=MEDIAN(IF(O4:O7="SJH8",IF(MONTH(P47)=5,E4:E7)))

entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP