Average a closed workbook (values>0)

[Jay]

I'm trying to average a range in another workbook but I want the average
to work when the source workbook is closed. I also only want to averag
numbers more than zero.

I'm currently using DAVERAGE which returns a #VALUE because of the
closed workbook.

Can anyone suggest an alternative. I know SUMPRODUCT works with closed
files, and I've no doubt it could probably be used to average values>0,
but I can't figure it out.

Many thanks


Jason

 

[Dave Peterson]

I opened both workbooks--just to make it easier and used this array formula:

=AVERAGE(IF([Book1.xls]Sheet2!C1:C10>0,[Book1.xls]Sheet2!C1:C10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

Then I closed book1.xls and tested it by:
selecting the cell with the formula
hit f2
followed by ctrl-shift-enter

It updated ok.

The formula with the closed workbook looked like this:

=AVERAGE(IF('C:\My Documents\excel\[book1.xls]Sheet2'!C1:C10>0,
'C:\My Documents\excel\[book1.xls]Sheet2'!C1:C10))

(still an array formula)

 

[John Smith]

I'm trying to average a range in another workbook but I want the average
to work when the source workbook is closed. I also only want to averag
numbers more than zero.

I'm currently using DAVERAGE which returns a #VALUE because of the
closed workbook.

Can anyone suggest an alternative. I know SUMPRODUCT works with closed
files, and I've no doubt it could probably be used to average values>0,
but I can't figure it out.

Many thanks


Jason