Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
C. C. is offline
external usenet poster
 
Posts: 14
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
C. C. is offline
external usenet poster
 
Posts: 14
Default 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!
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
C. C. is offline
external usenet poster
 
Posts: 14
Default 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!


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add from multiple criteria in a range thomsonpa Excel Discussion (Misc queries) 5 February 5th 09 06:21 PM
sum a range with multiple criteria Dianne Excel Discussion (Misc queries) 5 September 7th 08 04:09 PM
match multiple criteria ina range from multiple criteria multiplet RG Excel Worksheet Functions 8 September 28th 07 04:21 AM
look up with multiple criteria and within a range kjguillermo Excel Worksheet Functions 3 September 26th 06 12:23 AM
Counting from one range to another range, multiple criteria macamarr Excel Discussion (Misc queries) 3 June 10th 06 11:02 AM


All times are GMT +1. The time now is 03:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"