# 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

Hi,
like this?
Capture1.JPG

see attached

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.

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...

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!

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!

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.

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

Thanks, I just realized that!

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

#### 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