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

1. ## 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  Register To Reply

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

Hi,
like this?
Capture1.JPG

see attached  Register To Reply

3. ## 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.  Register To Reply

4. ## Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells Originally Posted by Tony Valko 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...  Register To Reply

5. ## Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells Originally Posted by TudyBTH Hi,
like this?
Attachment 479253

see attached
thanks for that!  Register To Reply

6. ## Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells Originally Posted by potejam thanks for that!
That formula does not return the correct results!  Register To Reply

7. ## Re: Calculating MEDIAN using "count" cells to determine frequency of "value" cells Originally Posted by potejam 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.  Register To Reply

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

Thanks, I just realized that!  Register To Reply