Averaging Cells from another workbook
 

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 of 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 values 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 in there
is an "X" so that is why the ISNUMBER function is there. I would like
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't
count it in the average.

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

Jason


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=557104

Averaging Cells from another workbook
 

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" wrote:


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 of 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 values 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 in there
is an "X" so that is why the ISNUMBER function is there. I would like
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't
count it in the average.

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

Jason


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=557104

Averaging Cells from another workbook
 

I tried by selecting all tabs and it was setup the same as the formula I
attempted below. Same result. I then just put sheets 1 and 2 together
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 which
isn't correct. But when I remove the additional tabs (sheets) and just
use one...then it works just fine. Any other suggestions???

MCDST070-271 Wrote:
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" wrote:


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 of
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 values
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 in
there
is an "X" so that is why the ISNUMBER function is there. I would
like
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't
count it in the average.

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

Jason


--
drvortex

------------------------------------------------------------------------
drvortex's Profile:
http://www.excelforum.com/member.php...o&userid=15896
View this thread:
http://www.excelforum.com/showthread...hreadid=557104




--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=557104