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

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

Originally Posted by TudyBTH

Hi,
like this?
Attachment 479253

see attached

thanks for that!

Thanks, I just realized that!