=AVERAGE(IF(N(INDIRECT("'"&H1:H6&"'!B4"))<0,N(IND IRECT("'"&H1:H6&"'!B4"))))

The Indirect function evaluates to an array of sheet ranges (even though the
range size is a single cell). Without the N function this would cause a
#VALUE! error. I've seen some people refer to this as "dereferencing". So
the N function passes the array as the numeric values. Not much of an
explanation but I don't know the exact technical reason. I just know that
this behavior is present and how to get around it. Harlan Grove can explain
it really well in technical terms.

Biff

"madduck" wrote in
message ...

OMG !!

Thanks for the Reply Biff,

as you can see I tried that already ;) (just with different cell
range)


But thanks to your Post I noticed that my formula had a space between N
& (

after removing this it now works.... yah !

If anyone can explain HOW this formula works, I would also apprieciate
it, I hate using things without know why

anyway thanks again Biff


--
madduck
------------------------------------------------------------------------
madduck's Profile:
http://www.excelforum.com/member.php...o&userid=36313
View this thread: http://www.excelforum.com/showthread...hreadid=560951