MEDIAN
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: MEDIAN

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Jackson, MI
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need to compute the MEDIAN value for the following list, but I must ignore the blanks and FALSE (it skews the result).

    thank you !


    $28.95
    $28.00
    $-
    $28.44
    $27.40
    $27.92
    $28.44
    $27.92
    $-
    $-
    $26.88
    $-
    $27.92
    $-
    $27.50
    FALSE
    $-
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried =MEDIAN(A1:A10) where A1 to A10 contains the values I want to compute. I included FALSE and blanks and it worked fine

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Jackson, MI
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you do it that way you get $27.45

    The true value is $27.92 (by excluding the blanks and 0).

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-11 08:05, gronkette1 wrote:
    I need to compute the MEDIAN value for the following list, but I must ignore the blanks and FALSE (it skews the result).

    thank you !


    $28.95
    $28.00
    $-
    $28.44
    $27.40
    $27.92
    $28.44
    $27.92
    $-
    $-
    $26.88
    $-
    $27.92
    $-
    $27.50
    FALSE
    $-
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    FALSE
    Array-enter:

    =MEDIAN(IF(A1:A24,A1:A24))

    where A1:A24 houses the target data.

    In order to array-enter a formula, you need to hit control+shift+enter, no just enter.

    I edited the array-formula, because it appears that you want to exclude the 0 values.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-11 08:25 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Jackson, MI
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I still get $27.45.... the true value should be $27.92...

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 09:50, gronkette1 wrote:
    I still get $27.45.... the true value should be $27.92...
    With Aladin's formula non array-enetered I get 27.45.

    With it array-entered, I get #VALUE! though it seems it should work.

    When I array-entered
    =MEDIAN(IF(A1:A24<>0,A1:A24))

    the formula returned 27.92 for me.

    Please make sure you are correctly entering this as an array formula.

    Bye,
    Jay

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 09:50, gronkette1 wrote:
    I still get $27.45.... the true value should be $27.92...

    Hi gronkette1:
    Your data set may be mixed up ... I multiplied your data set by 1 and then took the median and I did get
    =MEDIAN(A1:A24) to give 27.92

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 10:17, Yogi Anand wrote:
    On 2002-04-11 09:50, gronkette1 wrote:
    I still get $27.45.... the true value should be $27.92...

    Hi gronkette1:
    Your data set may be mixed up ... I multiplied your data set by 1 and then took the median and I did get
    =MEDIAN(A1:A24) to give 27.92

    Hi Yogi,

    Did you copy the data from the post? If so, you have the $- (blanks in the OP's terms) being treated as text, which is ignored with the MEDIAN function. That is why the problem is the zero values and not the FALSE cells.

    Change the $- to zero and you should get 27.45.

    Regards,
    Jay

    P.S. I did the exact same thing as I suspect you did.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Jackson, MI
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You guys are the greatest!!!

    I used {=MEDIAN(IFL4:L41<>0,L4:L41))}

    and it returned $27.92

    thank you, thank you!

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-11 10:35, gronkette1 wrote:
    You guys are the greatest!!!

    I used {=MEDIAN(IFL4:L41<>0,L4:L41))}

    and it returned $27.92

    thank you, thank you!
    Hi gronkette1:
    Just a minor editorial and substantative:

    editorial: the formula actually is
    {=MEDIAN(IF(L4:L41<>0,L4:L41))}

    substantative: you don't actually need <>0
    {=MEDIAN(IF(L4:L41,L4:L41))}



    Hi

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com