Calculating averages to ignore cells with formulas

[lost&confused12]

Hello,

I have quite a messy proble to sort out, I am currently using the following formula to calculate the average of cells but ignoring cells with zero and blanks, and to return 0 if an error occurs:

=IF(ISERROR(SUM(J19:J23)/((COUNTA(J19:J23))-(COUNTIF(J19:J23,0)))),0,SUM(J19:J23)/((COUNTA(J19:J23))-(COUNTIF(J19:J23,0))))

However, the cells used to calculate averages contains a formula (a simpe sum formula), is there a way to change the formula above to ignore the formula as well?

My aim is that users can put in values that J19:J23 will calulate and an average of J19:J23 calulated.

Any help appreciated

[oldchippy]

Hi,

Try this

=SUM(J19:J23)/MAX(1,COUNT(J19:J23)-COUNTIF(J19:J23,0))