Is there formula similar to SUMIF that will work for average?
 

Is there a function or formula similar to SUMIF that will work for average,
median, & mode?

I have an array of numbers with corresponding values similar to the following

Nbhd Size
3401 .23
3401 .25
3401 .28
3402 .20
3402 .21
3402 .29

The difficulty is there are hundreds of neighborhoods and thousands of size
values in the array. There is a serious time crunch and the task is too
labor intensive to complete in the time allotted.

Does anyone have any suggestions?

Thanks,

JohnU

Is there formula similar to SUMIF that will work for average?
 

In 2007 there is. Prior to that you'll have to use array functions. Take a
look here ...

http://cpearson.com/excel/ArrayFormulas.aspx

HTH

--
Zack Barresse




"johnu" wrote in message
...
Is there a function or formula similar to SUMIF that will work for
average,
median, & mode?

I have an array of numbers with corresponding values similar to the
following

Nbhd Size
3401 .23
3401 .25
3401 .28
3402 .20
3402 .21
3402 .29

The difficulty is there are hundreds of neighborhoods and thousands of
size
values in the array. There is a serious time crunch and the task is too
labor intensive to complete in the time allotted.

Does anyone have any suggestions?

Thanks,

JohnU

Is there formula similar to SUMIF that will work for average?
 

You can use array formulas** :

=AVERAGE(IF(A2:A7=3401,B2:B7))
=MEDIAN(IF(A2:A7=3401,B2:B7))
=MODE(IF(A2:A7=3401,B2:B7))

Note that based on your small sample the MODE formula would return an error
because there is no mode.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"johnu" wrote in message
...
Is there a function or formula similar to SUMIF that will work for
average,
median, & mode?

I have an array of numbers with corresponding values similar to the
following

Nbhd Size
3401 .23
3401 .25
3401 .28
3402 .20
3402 .21
3402 .29

The difficulty is there are hundreds of neighborhoods and thousands of
size
values in the array. There is a serious time crunch and the task is too
labor intensive to complete in the time allotted.

Does anyone have any suggestions?

Thanks,

JohnU