=SUMPRODUCT((A2:A100<4)+(A2:A10014),A2:A100)/SUMPRODUCT(((A2:A100<4)+(A2:A1
0014))*(ISNUMBER(A2:A100)))
or
=AVERAGE(IF(((A2:A100<4)+(A2:A10014))*(ISNUMBER(A 2:A100)),A2:A100))
the latter entered with ctrl + shift & enter
replace A2:A100 with your range (do not use the whole range A:A since the
formula won't work with that, use a range size you pretty much know won't be
larger like maybe A2:A1000 or something)
--
Regards,
Peo Sjoblom
"Deb Pingel" wrote
in message ...
Does anyone know how to average a range of numbers based on a criteria,
where the numbers have to fall within a range of data? I have tried
combining a number of statements and nothing is working. I want to
average a column of numbers that will have data added to it every day,
but throw out anything over 4 and under 14.
--
Deb Pingel
------------------------------------------------------------------------
Deb Pingel's Profile:
http://www.excelforum.com/member.php...o&userid=20119
View this thread: http://www.excelforum.com/showthread...hreadid=495354