Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
I want to get the MEDIAN of the values in F2:Q107 only if the value in
D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN. I don't want to use a pivot table if I can avoid it. Thanks in advance, Barb Reinhardt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
Try the following formulas, which needs to be confirmed with
CONTROL+SHIFT+ENTER... =MEDIAN(IF(D2:D107=D111,F2:Q107)) and =STDEV(IF(D2:D107=D111,F2:Q107)) Hope this helps! In article , Barb Reinhardt wrote: I want to get the MEDIAN of the values in F2:Q107 only if the value in D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN. I don't want to use a pivot table if I can avoid it. Thanks in advance, Barb Reinhardt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
This does what I want, thanks. How do I copy it over a bunch of cells? DO
I have to confirm each and every one? "Domenic" wrote: Try the following formulas, which needs to be confirmed with CONTROL+SHIFT+ENTER... =MEDIAN(IF(D2:D107=D111,F2:Q107)) and =STDEV(IF(D2:D107=D111,F2:Q107)) Hope this helps! In article , Barb Reinhardt wrote: I want to get the MEDIAN of the values in F2:Q107 only if the value in D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN. I don't want to use a pivot table if I can avoid it. Thanks in advance, Barb Reinhardt |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
I have cells in F2:Q107 that are blank and the calculations appear to treat
those as zeros. What needs to be changed. "Domenic" wrote: Try the following formulas, which needs to be confirmed with CONTROL+SHIFT+ENTER... =MEDIAN(IF(D2:D107=D111,F2:Q107)) and =STDEV(IF(D2:D107=D111,F2:Q107)) Hope this helps! In article , Barb Reinhardt wrote: I want to get the MEDIAN of the values in F2:Q107 only if the value in D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN. I don't want to use a pivot table if I can avoid it. Thanks in advance, Barb Reinhardt |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
In article ,
Barb Reinhardt wrote: This does what I want, thanks. You're welcome! Glad I could help! How do I copy it over a bunch of cells? If you're copying the formula down the column... =MEDIAN(IF(D$2:D$107=D111,F$2:Q$107)) If you're copying the formula across the row... =MEDIAN(IF($D2:$D107=D111,$F2:$Q107)) DO I have to confirm each and every one? No, you only need to confirm with CONTROL+SHIFT+ENTER for the first cell, then you can copy or drag the formula down or across... Hope this helps! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
Try...
=MEDIAN(IF(D$2:D$107=D111,IF(F$2:Q$107<"",F$2:Q$1 07))) Hope this helps! In article , Barb Reinhardt wrote: I have cells in F2:Q107 that are blank and the calculations appear to treat those as zeros. What needs to be changed. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MEDIAN, STDEV of a range IF another range = specific value
Now why didn't I think of that. I tried using IF(AND()) and it didn't like
what I had. "Domenic" wrote: Try... =MEDIAN(IF(D$2:D$107=D111,IF(F$2:Q$107<"",F$2:Q$1 07))) Hope this helps! In article , Barb Reinhardt wrote: I have cells in F2:Q107 that are blank and the calculations appear to treat those as zeros. What needs to be changed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting specific row/column from a named range | Excel Worksheet Functions | |||
How to sum data that is only within a specific number range? | Excel Worksheet Functions | |||
How do I add a range of numbers to sum a specific total? | Excel Worksheet Functions | |||
Displaying value of specific cell within a range, with IF function...? | Excel Discussion (Misc queries) | |||
find numbers in a range that add to a specific value | Excel Discussion (Misc queries) |