average function

Hi,

If I have a list of random numbers down columnA, how would I write up a formula in another cell that calculates an average of the figures greater than a certain figure (say, 0) but less than another figure (500000).

Thanks

this is an ARRAY formula so must be entered with ctrl+shift+enter
=AVERAGE(IF((A2:A22>0)*(A2:A22<100),A2:A22))

--
Don Guillett
SalesAid Software
[email protected]
"inoexcel" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> If I have a list of random numbers down columnA, how would I write up a
> formula in another cell that calculates an average of the figures
> greater than a certain figure (say, 0) but less than another figure
> (500000).
>
> Thanks
>
>
> --
> inoexcel
> ------------------------------------------------------------------------
> inoexcel's Profile:
> http://www.excelforum.com/member.php...o&userid=33055
> View this thread: http://www.excelforum.com/showthread...hreadid=533818
>

Great, thanks!

glad it helped

--
Don Guillett
SalesAid Software
[email protected]
"inoexcel" <[email protected]> wrote in
message news:[email protected]...
>
> Great, thanks!
>
>
> --
> inoexcel
> ------------------------------------------------------------------------
> inoexcel's Profile:
> http://www.excelforum.com/member.php...o&userid=33055
> View this thread: http://www.excelforum.com/showthread...hreadid=533818
>

You can use the DAVERAGE function.

The function is =DAVERAGE(database,field,criteria)


For example:

A B
1 Random Random
2 >0 <500000
3 Random
4 54
5 454540654
6 45345
7 11864
8 154
9 0
10 -154
11 15053


=DAVERAGE(A3:A11, "Random", A1:B2)

Hope it works for you.


"inoexcel" wrote:

>
> Hi,
>
> If I have a list of random numbers down columnA, how would I write up a
> formula in another cell that calculates an average of the figures
> greater than a certain figure (say, 0) but less than another figure
> (500000).
>
> Thanks
>
>
> --
> inoexcel
> ------------------------------------------------------------------------
> inoexcel's Profile: http://www.excelforum.com/member.php...o&userid=33055
> View this thread: http://www.excelforum.com/showthread...hreadid=533818
>
>