Average Negatives and ignore 0

[The Animal]

Hi
I have a range of cells Q5,V5,AA5,AF5 etc that are all negative values but some are zero or blank.
I need a formula that gives me an average of the selected cells but ignoring the zero or blank cells in the averaging.

Any help would be great

Thanks Stephen

 

[barry houdini]

Try this formula

=IFERROR(SUM( Q5,V5,AA5,AF5)/((Q5<0)+(V5<0)+(AA5<0)+(AF5<0)),"")

 

[The Animal]

Thanks so much Barry.
There is a number of cells used in the calculation which are each 4th cell (G5,V5,AA5,AF5 etc) starting at Q5 and finishing at JL5.
Is there a way where the formula can use each 4th cell from Q5:JL5 instead of having to include each cell ((Q5<0)+(V5<0)+(AA5<0)+(AF5<0)) etc in the formula?
Thanks again Stephen

 

[barry houdini]

Q5,V5,AA5,AF5 etc

Ah, I missed the etc.!

It's every 5 cells, though......

Try using this "array formula"

=AVERAGE(IF(Q5:JL5<0, IF(MOD(COLUMN(Q5:JL5)-COLUMN(Q5),5)=0,Q5:JL5)))

confirm with CTRL+SHIFT+ENTER

 

[The Animal]

Ooops sorry Barry, thats why I ask the questions and you answer them HaHa
Works great thanks