CSE PROBLEM: Taking the average if #N/A is present

[the_mitten]

Hi all,

I am using a CSE (Control-Shift-Enter) formula to calculate the average of 3 cells. {=AVERAGE(IF(($BK7:$BM7)<>0,$BK7:$BM7))}

My formula is great since I left a cell in my range blank if there were no values. HOWEVER, when I plotted my averages in a line graph, the blank values plummeted to zero. SO, I modified my formulas to produce an #N/A so that the zero values would not show up. Problem is, now my CSE formula will not recognize a #N/A when it tries to take the average.

Any ideas guys?

 

[fairwinds]

Hi,
If you cannot get rid of #N/A you could:

=AVERAGE(IF(ISNUMBER($BK7:$BM7),IF($BK7:$BM7<>0,$BK7:$BM7)))

(Array enter!)

 

[the_mitten]

Fairwinds,

Great tip. It worked.

Thanks

ray: