+ Reply to Thread
Results 1 to 8 of 8

Calculating MEDIAN using "count" cells to determine frequency of "value" cells

  1. #1
    Registered User
    Join Date
    11-01-2009
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    33

    Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Hi everyone,

    Basically I want to calculate the Median of a list of values, which are in column B. But in column C I have "count" data, which represents how often the values in column B are represented in the number list, ranging anywhere from 1 to 30. In column A I have a qualifier, which I think will be fairly easy to incorporate once I get the multiple occurrence bit. For example

    QUALIFIER VALUE COUNT
    Cats 4 2
    Dogs 5 3
    Cats 9 1
    Cats 5 3
    Dogs 4 1

    So what I'm trying to do is get the Median value for Cats, while incorporating both the value and count data. The actual string would look something like this:
    Cats: 4 4 9 5 5 5

    Is there any way I can get that from how my data is organized?
    Thanks

  2. #2
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Hi,
    like this?
    Capture1.JPG

    see attached
    Attached Files Attached Files

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    QUALIFIER
    VALUE
    COUNT
    ------
    QUALIFIER
    Median
    2
    Cats
    4
    2
    Cats
    5
    3
    Dogs
    5
    3
    4
    Cats
    9
    1
    5
    Cats
    5
    3
    6
    Dogs
    4
    1


    This array formula** entered in F2:

    =MEDIAN(IF(A2:A6=E2,IF(COLUMN(INDIRECT("1:30"))<=C2:C6,B2:B6)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-01-2009
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    QUALIFIER
    VALUE
    COUNT
    ------
    QUALIFIER
    Median
    2
    Cats
    4
    2
    Cats
    5
    3
    Dogs
    5
    3
    4
    Cats
    9
    1
    5
    Cats
    5
    3
    6
    Dogs
    4
    1


    This array formula** entered in F2:

    =MEDIAN(IF(A2:A6=E2,IF(COLUMN(INDIRECT("1:30"))<=C2:C6,B2:B6)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Thanks, that seems to do the trick. However, I suppose I should have mentioned that I have over 800 rows. That appears to prevent the array formula from running due to Excel running out of resources...

  5. #5
    Registered User
    Join Date
    11-01-2009
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Quote Originally Posted by TudyBTH View Post
    Hi,
    like this?
    Attachment 479253

    see attached
    thanks for that!

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Quote Originally Posted by potejam View Post
    thanks for that!
    That formula does not return the correct results!

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Quote Originally Posted by potejam View Post
    Thanks, that seems to do the trick. However, I suppose I should have mentioned that I have over 800 rows. That appears to prevent the array formula from running due to Excel running out of resources...
    This should calculate much faster.

    Create this named formula.

    Name: Array
    Refers to:

    ={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,
    17,18,19,20,21,22,23,24,25,26,27,28,29,30}

    Then, the formula becomes:

    =MEDIAN(IF(A2:A6=E2,IF(Array<=C2:C6,B2:B6)))

    Still array entered.

  8. #8
    Registered User
    Join Date
    11-01-2009
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells

    Thanks, I just realized that!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-20-2015, 01:13 PM
  2. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  5. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  6. Why does =countif(range,"<>""") count empty cells?
    By Ingeniero1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2008, 01:40 PM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1