Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find Median of Positive numbers only in Range
I have a column containing both positive and negative numbers
I need a formula to find the Median of all positive numbers, and a separate formula to find the Median of all negative numbers Any help gratefully acknowledged |
#2
|
|||
|
|||
"MichaelC" wrote: I have a column containing both positive and negative numbers I need a formula to find the Median of all positive numbers, and a separate formula to find the Median of all negative numbers Any help gratefully acknowledged The following are array formulas entered with cntrl+shft+enter =MEDIAN(IF(B2:B130,B2:B13)) =MEDIAN(IF(B2:B13<0,B2:B13)) Peter atherton |
#3
|
|||
|
|||
On Thu, 23 Jun 2005 17:23:11 -0700, "MichaelC"
wrote: I have a column containing both positive and negative numbers I need a formula to find the Median of all positive numbers, and a separate formula to find the Median of all negative numbers Any help gratefully acknowledged Will there be 0's? With no 0's, then the **array** formula: =MEDIAN(IF(rng0,rng)) for positive numbers and =MEDIAN(IF(rng<0,rng)) for negative numbers. To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. If 0's will be included as positive numbers, then something like: =MEDIAN(IF((rng=0)*(rng<""),rng)) (also array-entered) should do the trick. --ron |
#4
|
|||
|
|||
Thank you very much Ron and Peter.
"Ron Rosenfeld" wrote: On Thu, 23 Jun 2005 17:23:11 -0700, "MichaelC" wrote: I have a column containing both positive and negative numbers I need a formula to find the Median of all positive numbers, and a separate formula to find the Median of all negative numbers Any help gratefully acknowledged Will there be 0's? With no 0's, then the **array** formula: =MEDIAN(IF(rng0,rng)) for positive numbers and =MEDIAN(IF(rng<0,rng)) for negative numbers. To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. If 0's will be included as positive numbers, then something like: =MEDIAN(IF((rng=0)*(rng<""),rng)) (also array-entered) should do the trick. --ron |
#5
|
|||
|
|||
On Thu, 23 Jun 2005 18:26:01 -0700, "MichaelC"
wrote: Thank you very much Ron and Peter. You're welcome. Glad to help. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum only positive numbers | Excel Discussion (Misc queries) | |||
add only positive numbers and avg in a column | Excel Worksheet Functions | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) | |||
find numbers in a range that add to a specific value | Excel Discussion (Misc queries) | |||
How do you find duplicate values in excel- 2 columns of numbers | Excel Discussion (Misc queries) |