Paste link problem
 

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

Paste link problem
 

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

"Norm Shea" <Norm wrote in message
...
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

Paste link problem
 

Bernard,

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

Norm

"Bernard Liengme" wrote:

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

"Norm Shea" <Norm wrote in message
...
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