MedianIf multiple range and criteria
 

Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:C alc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$50000),Calc!$E$3:Calc!$E$5000) )

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E50000 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

MedianIf multiple range and criteria
 

If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A1 9,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000 0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP


"C." wrote in message
...
Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:C alc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$50000),Calc!$E$3:Calc!$E$5000) )

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E50000 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

MedianIf multiple range and criteria
 

On Dec 10, 5:27*pm, "T. Valko" wrote:
If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A1 9,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000 0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

"C." wrote in message

...

Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:C alc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$50000),Calc!$E$3:Calc!$E$5000) )

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E50000 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

MedianIf multiple range and criteria
 

Try this...

Array enterd:

=MEDIAN(IF((Calc!$A$3:$A$5000=A5)+(Calc!$A$3:$A$50 00=A11)0,IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5 0000,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP


"C." wrote in message
...
On Dec 10, 5:27 pm, "T. Valko" wrote:
If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A1 9,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000 0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

"C." wrote in message

...

Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:C alc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$50000),Calc!$E$3:Calc!$E$5000) )

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E50000 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

MedianIf multiple range and criteria
 

On Dec 11, 12:22*am, "T. Valko" wrote:
Try this...

Array enterd:

=MEDIAN(IF((Calc!$A$3:$A$5000=A5)+(Calc!$A$3:$A$50 00=A11)0,IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5 0000,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

"C." wrote in message

...
On Dec 10, 5:27 pm, "T. Valko" wrote:



If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A1 9,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000 0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

"C." wrote in message

....

Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:C alc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$50000),Calc!$E$3:Calc!$E$5000) )

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E50000 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

Thank you! That worked perfectly!

MedianIf multiple range and criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"C." wrote in message
...
On Dec 11, 12:22 am, "T. Valko" wrote:
Try this...

Array enterd:

=MEDIAN(IF((Calc!$A$3:$A$5000=A5)+(Calc!$A$3:$A$50 00=A11)0,IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5 0000,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

"C." wrote in message

...
On Dec 10, 5:27 pm, "T. Valko" wrote:



If I understood what you want...

Array entered:

=MEDIAN(IF(ISNUMBER(MATCH(Calc!$A$3:$A$5000,A17:A1 9,0)),IF(Calc!$C$3:$C$5000=1,IF(Calc!$E$3:$E$5000 0,Calc!$E$3:$E$5000))))

--
Biff
Microsoft Excel MVP

"C." wrote in message

...

Hi all,

I have this formula that works perfectly for one criteria for the
range A3:A5000:

=MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:C alc!$A$5000=A19)*
(Calc!$E$3:Calc!$E$50000),Calc!$E$3:Calc!$E$5000) )

I need to modify this formula in order to scan the range A3:A5000 and
if any values in this range are equal to A17 and A18 and A19, I need
it to return the median of the range E3:E50000 AND that also
satisfies the condition C3:C5000=1.

Is this possible? Any thoughts? Does this make sense?

Thanks!

Excellent! That was perfect. Thank you so much!

One last question - how could I do it if it the range wasn't
sequential. That is, instead of A17:A19, I need it to be A11 and A5?

Thanks again!

Thank you! That worked perfectly!