Assuming your values are in column B:

Try:

=AVERAGE(IF(INDEX(B1:B100,SUMPRODUCT(LARGE(ROW(B1: B100)*(B1:B100<0),3))):B100,INDEX(B1:B100,SUMPROD UCT(LARGE(ROW(B1:B100)*(B1:B100<0),3))):B100))

Confirmed with CTRL+SHIFT+ENTER....not just ENTER (this is an array
formula).

Adjust ranges to accomodate all future entries (include header row).


If you have Excel 2003, then you can convert your current list
(assuming it's range is B1:B10 (including header row) to a List via
Data|List and then use this formula

=AVERAGE(IF(INDEX(B1:B10,SUMPRODUCT(LARGE(ROW(B1:B 10)*(B1:B10<0),3))):INDIRECT("B"&MATCH(9.9999999E +307,B1:B10)),INDEX(B1:B10,SUMPRODUCT(LARGE(ROW(B1 :B10)*(B1:B10<0),3))):INDIRECT("B"&MATCH(9.999999 9E+307,B1:B10))))

also confirmed with the CSE key combo.

With List feature, when you add more records, the formula will update
it's range automatically.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=558513