Is there any way to get an AVERAGEIF & MEDIANIF function in Exce

T

TinaMo

Hey thanks...but what about the MEDIANIF? Do you know if MS Excel 2003 has
a MEDIANIF function?
 
B

Bernie Deitrick

Tina,

Array enter (using Ctrl-Shift-Enter) a formula like:
=MEDIAN(IF($B$2:$B$200=E2,$C$2­:$C$200))
That will return the median of numbers in C2:C200 where B2:B200 matched cell
E2.

For AverageIf, use SumIF/CountIF.

HTH,
Bernie
MS Excel MVP
 
T

TinaMo

Thanks,

I will try it.

Bernie Deitrick said:
Tina,

Array enter (using Ctrl-Shift-Enter) a formula like:
=MEDIAN(IF($B$2:$B$200=E2,$C$2­:$C$200))
That will return the median of numbers in C2:C200 where B2:B200 matched cell
E2.

For AverageIf, use SumIF/CountIF.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
T

tralbert

TinaMo,

Have you tried using MegaStat? It's an Excel Add-on that allows you to do
all kinds of special statistical functions in Excel. Very worth the $.

Good Luck!
 
B

Bob Phillips

=AVERAGE(IF(rng=some_condition,rng))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top