| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add from multiple criteria in a range | Excel Discussion (Misc queries) | |||
| sum a range with multiple criteria | Excel Discussion (Misc queries) | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| look up with multiple criteria and within a range | Excel Worksheet Functions | |||
| Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) | |||
Hybrid Mode
