Averaging Cells from another workbook

[drvortex]

Hi all,

I'm going to give this another shot and try to state it a differen
way.

This is what I have so far:

{=AVERAGE(IF(ISNUMBER('*[Jul06AMEFVER.xls]1'*!$B16:$L16),ABS('[Jul06AMEFVER.xls]1'!$B16:$L16)))}

What this is stating is I want to average the absolute value of cell
B16:L16 only if the cell contains a number. The good thing is tha
this works; however, you see in the *bold* part that it is onl
referring to TAB 1 in the Jul06AMEFVER.xls workbook.

My problem is that I want to take the average of all absolute values o
cells B16:L16 on TAB 1 through TAB 31. I tried this:

{=AVERAGE(IF(ISNUMBER('[Jul06AMEFVER.xls]*1:31'*!$B16:$L16),ABS('[Jul06AMEFVER.xls]*1:31'*!$B16:$L16)))}

But the answer is "0". I checked when the data isn't filled in ther
is an "X" so that is why the ISNUMBER function is there. I would lik
the formula to do an ongoing average while the data is being filled in
If the referenced cell (aka B16:L16) as an "X" or blank space don'
count it in the average.

I hope you all understand what I'm trying to do. Been working on thi
for two days now and running out of ideas. Thoughts. Thanks so much.

Jaso

 

[Guest]

Have you tried selecting all tabs first, then applying the formula to all tabs?

To select all tabs, in this case 1 through 31, select tab 1, then hold the
shift key and click on every other effected tab to which the formula applies.

 

[drvortex]

I tried by selecting all tabs and it was setup the same as the formula
attempted below. Same result. I then just put sheets 1 and 2 togethe
which only has a total of 5 cells to average and the result is "0".
The numbers were 0, 5, 5, 1, 1. This gave me an average of zero whic
isn't correct. But when I remove the additional tabs (sheets) and jus
use one...then it works just fine. Any other suggestions???

Have you tried selecting all tabs first, then applying the formula t
all tabs?

To select all tabs, in this case 1 through 31, select tab 1, then hol
the
shift key and click on every other effected tab to which the formul
applies.

Hi all,

I'm going to give this another shot and try to state it a different
way.

This is what I have so far:

{=AVERAGE(IF(ISNUMBER('*[Jul06AMEFVER.xls]1'*!$B16:$L16),ABS('[Jul06AMEFVER.xls]1'!$B16:$L16)))}

What this is stating is I want to average the absolute value o cells
B16:L16 only if the cell contains a number. The good thing is that
this works; however, you see in the *bold* part that it is only
referring to TAB 1 in the Jul06AMEFVER.xls workbook.

My problem is that I want to take the average of all absolute value of
cells B16:L16 on TAB 1 through TAB 31. I tried this:

{=AVERAGE(IF(ISNUMBER('[Jul06AMEFVER.xls]*1:31'*!$B16:$L16),ABS('[Jul06AMEFVER.xls]*1:31'*!$B16:$L16)))}

But the answer is "0". I checked when the data isn't filled i there
is an "X" so that is why the ISNUMBER function is there. I woul like
the formula to do an ongoing average while the data is being fille in.
If the referenced cell (aka B16:L16) as an "X" or blank space don't
count it in the average.

I hope you all understand what I'm trying to do. Been working o this
for two days now and running out of ideas. Thoughts. Thanks s much.

Jason