Using Average Function

G

galex58

excel keeps wanting to include cells that contain formulas linking it t
another spreadsheet in a average function. Raw data is placed i
spreadsheet A on a monthly basis. spreadsheet B takes data fro
spreadsheet A. Some months there is no data to long in, but o
spreadsheet B it where there is no data transfered from sheet A i
still looks at cells that contain the linking formula as a cell in th
it's average calculation.

example:
cells on sheet B a1 - m1 will contain linking formulas to sheet A. Som
of those cell will reflect the data entered on sheet A. Some cells wil
be empty because there was no data entered in the referenced cells o
sheet A. The problem is the average funtion is counting all cells i
the calculation including those that contain only the linking formula
How can I stop excel from doing this
 
P

Peo Sjoblom

Instead of linking directly like

=Sheet3!A1

use

=IF(Sheet3!A1="","",Sheet3!A1)

or

=AVERAGE(IF(Range<>0,Range))

entered with ctrl + shift & enter
that would of course mean that there are no real zeros in your range

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Top