Using 'Median' with array formula

  • Thread starter Thread starter Auser
  • Start date Start date
A

Auser

I have the following array formula:

{=SUM((Main!$G$2:$G$401>Lookup!Date1)*(Main!$G$2:$G$401<=Lookup!Date2)*(Main
!$E$2:$E$401="A N Other"))}

that gives me the correct answer (i.e. Number of occasions a user appears in
a given column between two dates).
I now want to add the Median for each user to another column like so:

{=SUM((Main!$G$2:$G$401>Lookup!$E$2)*(Main!$G$2:$G$401<=Lookup!$E$3)*(Main!$
E$2:$E$401="A N Other")*(MEDIAN(Main!$L$2:$L$401)))}

However this gives me the median for all users and not for just the name
specified. Any help/advice on how I can achieve this greatly appreciated.

Thanks
 
Hi
try
{=MEDIAN((Main!$G$2:$G$401>Lookup!$E$2)*(Main!
$G$2:$G$401<=Lookup!$E$3)*(Main!$
E$2:$E$401="A N Other")*(Main!$L$2:$L$401))}
 
try
{=MEDIAN((Main!$G$2:$G$401>Lookup!$E$2)*(Main!
$G$2:$G$401<=Lookup!$E$3)*(Main!$
E$2:$E$401="A N Other")*(Main!$L$2:$L$401))}
Click to expand...

The zeros produced by the unsatisfied criteria in

(Main!$G$2:$G$401>Lookup!$E$2)
*(Main!$G$2:$G$401<=Lookup!$E$3)
*(Main!$E$2:$E$401="A N Other")

would be included in the calculation of the median. Simplifying a bit, the
median of {3;4;5} is 4, and the conditional median given by the array formula

=MEDIAN(IF((ROW(1:10)>2)*(ROW(1:10)<6),ROW(1:10)))

is also 4, but the 'conditional' median given by the array formula

=MEDIAN((ROW(1:10)>2)*(ROW(1:10)<6)*ROW(1:10))

is, correctly, 0. OP should use the array formula

=MEDIAN(IF((Main!$G$2:$G$401>Lookup!$E$2)
*(Main!$G$2:$G$401<=Lookup!$E$3)
*(Main!$E$2:$E$401="A N Other"),
Main!$L$2:$L$401))
 
OP should use the array formula

=MEDIAN(IF((Main!$G$2:$G$401>Lookup!$E$2)
*(Main!$G$2:$G$401<=Lookup!$E$3)
*(Main!$E$2:$E$401="A N Other"),
Main!$L$2:$L$401))
Click to expand...

Thanks HG. I'll try that. Thanks to FK as well, but unfortunately it didn't
quite achieve the result I was looking for.
 
Back
Top