Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MichaelC
 
Posts: n/a
Default 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   Report Post  
PeterAtherton
 
Posts: n/a
Default



"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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
MichaelC
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
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
Sum only positive numbers Fred Holmes Excel Discussion (Misc queries) 2 May 1st 05 09:02 PM
add only positive numbers and avg in a column rich Excel Worksheet Functions 6 March 10th 05 10:19 AM
How can I change positive numbers to negative, i.e. change 50 to - godwingi Excel Discussion (Misc queries) 5 February 28th 05 06:41 PM
find numbers in a range that add to a specific value Brett Excel Discussion (Misc queries) 1 December 20th 04 02:55 PM
How do you find duplicate values in excel- 2 columns of numbers rickmanz Excel Discussion (Misc queries) 1 December 16th 04 12:16 AM


All times are GMT +1. The time now is 02:16 PM.

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"