averaging linked cells

[galex58]

I hope someone can help. I have been working on a project that contain
a number of worksheets that gets information off from a rawdata shee
that contains a lot of data. The problem I am having is when i try t
average a group of cells of cells that contain the link formula such a
cell a1='a'!B3,cell a2 ='a'!B4, cell a3='a'!B5. cell a4 will contai
the formula =average(a1.a3). If all the cells contain data from shee
"A" everything is fine, but if one of the cells does not then th
average for the two cells that do contain data is wrong. I can not tel
excel to only average cells that contain data. It keep counting th
cells to average by even those that contain foumulas and won't ignor
them in the calculation. Any help would be appreciated

 

[Peo Sjoblom]

You could fix that when you link the cells, i.e.
instead of ='a'!B3 use

=IF('a'!B3="","",'a'!B3)

that way it will return a blank and average disregards that
or you could average directly to the other sheet

=AVERAGE('a'!B3:B5)


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

Regards,

Peo Sjoblom

 

[hcj]

instead of setting a1= value directly, test if the linked
value is blank first:

=if(isblank(value),"",value)

HTH, hcj