SumIF
 

I would like to be able to use the same type of functionallity given with the
sum if function, but instead of returning the sum, return the median? Some
example data follows:

Column A Column B
B1 175
B1 200
B1 225

I would like the formula to use Column A as the column to evaluate, and
return the median of column B based on the criteria (in this case it would be
the median of 200 for the values in column B based on B1 in column A

Any ideas? Thanks.

SumIF
 

One set-up to try ..

Assume data would be in cols A and B,
from row1 down till row100 (expected max extent)

Put in D1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW()))

Put in E1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

Select D1:E1, copy down to E100

Col E extracts the list of uniques in col A

Put in F1, array-enter (press CTRL+SHIFT+ENTER):
=IF(E1="","",MEDIAN(IF(($A$1:$A$100=E1)*($A$1:$A$1 00<0),$B$1:$B$100)))

Copy F1 down until the last row of uniques extracted in col E

Col F returns the desired results for each of the uniques in col E

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jason" wrote in message
...
I would like to be able to use the same type of functionallity given with
the
sum if function, but instead of returning the sum, return the median?
Some
example data follows:

Column A Column B
B1 175
B1 200
B1 225

I would like the formula to use Column A as the column to evaluate, and
return the median of column B based on the criteria (in this case it would
be
the median of 200 for the values in column B based on B1 in column A

Any ideas? Thanks.

SumIF
 

Jason

With your data, assuming headings in row 1 and data beginning in row 2, use
in cell C2

=MEDIAN(IF(A$1:A$99=A2,B$1:B$9))

Now the catch. This is to be an array formula.

Instead of using ENTER to enter the formula, use

CTRL-SHIFT-ENTER.

You should then see curly braces {} around the formula.

{=MEDIAN(IF(A$1:A$99=A2,B$1:B$9))}




"Jason" wrote:

I would like to be able to use the same type of functionallity given with the
sum if function, but instead of returning the sum, return the median? Some
example data follows:

Column A Column B
B1 175
B1 200
B1 225

I would like the formula to use Column A as the column to evaluate, and
return the median of column B based on the criteria (in this case it would be
the median of 200 for the values in column B based on B1 in column A

Any ideas? Thanks.