Need a formula to average a range of cells but only if there are numbers
so I dont get a zero.
=AVERAGE(IF(E8:N8,E8:N8)) this is what I'v tried but I just get
#VALUE!
Thanx


--
ferg
------------------------------------------------------------------------
ferg's Profile: http://www.excelforum.com/member.php...o&userid=36519
View this thread: http://www.excelforum.com/showthread...hreadid=563563

Hi
tRY THIS ONE
=IF(ISERROR(AVERAGE(E8:N8)),0,AVERAGE(E8:N8))

"ferg" wrote:


Need a formula to average a range of cells but only if there are numbers
so I dont get a zero.
=AVERAGE(IF(E8:N8,E8:N8)) this is what I'v tried but I just get
#VALUE!
Thanx


--
ferg
------------------------------------------------------------------------
ferg's Profile: http://www.excelforum.com/member.php...o&userid=36519
View this thread: http://www.excelforum.com/showthread...hreadid=563563

the normal average function will ignore non numeric values

if there are no numbers in the range

if(iserror(average(e8:n8)),"",average(e8:n8))

otherwise the previous post but it will give 0 if the range is blank,
it depends what youwant

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563563

Yeah Rafeeks leaves a 0 as u said Dav so I want a blank cell if no
nums
So Thanx heeps guys worxs spot on :)


--
ferg
------------------------------------------------------------------------
ferg's Profile: http://www.excelforum.com/member.php...o&userid=36519
View this thread: http://www.excelforum.com/showthread...hreadid=563563