Paste link problem

[Guest]

I have two separate spreadsheet files, one that we collect field data in
using a Palm device and one that has graphs generated from that data. I've
created a Paste Link between the two files that covers cells A9:AG500. My
problem is, in the field data file, WaterLevels.xls, rows 300 to 500 are
blank and so with Paste Link, those rows in the data spreadsheet of the graph
file, WaterLevelGraphs.xls are filled with zeros. This causes a problem
because I have an AVG formula for rows 9 to 500. Is there any way to format
the cells in either the field file or the graph file so zeros do not appear?
Thanks

 

[Bernard Liengme]

How about =AVERAGE(IF(A9:AG500>0,A9:AG500,"")) entered as array formula with
SHIFT+CTRL+ENTER
best wishes

 

[Guest]

Bernard,

Thanks, that worked great. I'm not that familiar with array formulas.

Norm