software

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

How about =AVERAGE(IF(A9:AG500gt;0,A9:AG500,quot;quot;)) entered as array formula with
SHIFT CTRL ENTER
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

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

Norm

quot;Bernard Liengmequot; wrote:

gt; How about =AVERAGE(IF(A9:AG500gt;0,A9:AG500,quot;quot;)) entered as array formula with
gt; SHIFT CTRL ENTER
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Norm Sheaquot; lt;Norm gt; wrote in message
gt; ...
gt; gt;I have two separate spreadsheet files, one that we collect field data in
gt; gt; using a Palm device and one that has graphs generated from that data.
gt; gt; I've
gt; gt; created a Paste Link between the two files that covers cells A9:AG500. My
gt; gt; problem is, in the field data file, WaterLevels.xls, rows 300 to 500 are
gt; gt; blank and so with Paste Link, those rows in the data spreadsheet of the
gt; gt; graph
gt; gt; file, WaterLevelGraphs.xls are filled with zeros. This causes a problem
gt; gt; because I have an AVG formula for rows 9 to 500. Is there any way to
gt; gt; format
gt; gt; the cells in either the field file or the graph file so zeros do not
gt; gt; appear?
gt; gt; Thanks
gt;
gt;
gt;