Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are 0.
So, I've tried the following, but it doesn't work. (I have tried this both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

The easiest way would be to link to each of those cells like this:

=Jan!CT19
=Feb!CT19
=Mar!CT19
......
=Dec!CT19

Then just average that range.

Array entered:

=AVERAGE(IF(A1:A12,A1:A12))


--
Biff
Microsoft Excel MVP


"Ellen G" wrote in message
...
Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are
0.
So, I've tried the following, but it doesn't work. (I have tried this both
as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

That would certainly work, however I'd love to know if there is a way to do a
simple formula so I don't have to store in the information twice (once in the
individual cells and once on another sheet).

Thanks for your help.

Ellen

"Ellen G" wrote:

Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are 0.
So, I've tried the following, but it doesn't work. (I have tried this both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

Actually, I lied -- this won't work. A bit hard to explain why, but I can't
create individual cells for each month. So, I'm still looking forward a
formulat that would work.

Thoughts from anyone?

Thanks much.

Ellen

"Ellen G" wrote:

That would certainly work, however I'd love to know if there is a way to do a
simple formula so I don't have to store in the information twice (once in the
individual cells and once on another sheet).

Thanks for your help.

Ellen

"Ellen G" wrote:

Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are 0.
So, I've tried the following, but it doesn't work. (I have tried this both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

if there is a way to do a simple formula

Well, simple being relative! <g

Assuming the values to average are positive:

=SUM(Jan:Dec!CT19)/SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1:1 2"))*30,"mmm")&"!CT19"),"0"))

Or , if your OK with using an add-in...

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula:

=AVERAGE(IF(THREED(Jan:Dec!CT19),THREED(Jan:Dec!CT 19)))

--
Biff
Microsoft Excel MVP


"Ellen G" wrote in message
...
That would certainly work, however I'd love to know if there is a way to
do a
simple formula so I don't have to store in the information twice (once in
the
individual cells and once on another sheet).

Thanks for your help.

Ellen

"Ellen G" wrote:

Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are
0.
So, I've tried the following, but it doesn't work. (I have tried this
both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

Biff,

That link doesn't seem to work anymore. Any chance you have another way I
could get a hold of the "Threed" UDF. That thing could solve a lot of
problems for me.

Thanks,
Chad


"T. Valko" wrote:

if there is a way to do a simple formula

Well, simple being relative! <g

Assuming the values to average are positive:

=SUM(Jan:Dec!CT19)/SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1:1 2"))*30,"mmm")&"!CT19"),"0"))

Or , if your OK with using an add-in...

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula:

=AVERAGE(IF(THREED(Jan:Dec!CT19),THREED(Jan:Dec!CT 19)))

--
Biff
Microsoft Excel MVP


"Ellen G" wrote in message
...
That would certainly work, however I'd love to know if there is a way to
do a
simple formula so I don't have to store in the information twice (once in
the
individual cells and once on another sheet).

Thanks for your help.

Ellen

"Ellen G" wrote:

Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are
0.
So, I've tried the following, but it doesn't work. (I have tried this
both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

Try this alternative site:

http://www.download.com/Morefunc/300...-10423159.html

--
Biff
Microsoft Excel MVP


"Chad" wrote in message
...
Biff,

That link doesn't seem to work anymore. Any chance you have another way I
could get a hold of the "Threed" UDF. That thing could solve a lot of
problems for me.

Thanks,
Chad


"T. Valko" wrote:

if there is a way to do a simple formula

Well, simple being relative! <g

Assuming the values to average are positive:

=SUM(Jan:Dec!CT19)/SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1:1 2"))*30,"mmm")&"!CT19"),"0"))

Or , if your OK with using an add-in...

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula:

=AVERAGE(IF(THREED(Jan:Dec!CT19),THREED(Jan:Dec!CT 19)))

--
Biff
Microsoft Excel MVP


"Ellen G" wrote in message
...
That would certainly work, however I'd love to know if there is a way
to
do a
simple formula so I don't have to store in the information twice (once
in
the
individual cells and once on another sheet).

Thanks for your help.

Ellen

"Ellen G" wrote:

Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb,
Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that
are
0.
So, I've tried the following, but it doesn't work. (I have tried this
both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

Version 5.0 of Laurent Longre's Morefunc.xll, from 28 October 2007,
can be downloaded he

http://downloads.zdnet.com/abstract.aspx?docid=211056

You have to register to download from that site, but registration is
free.

I happened to spend a bit of time and detective work tracking it down
a couple of weeks ago, as I figured there might be a newer version than
the 2006 one I had been using. It's a great add-in.
I haven't quite figured out how to use the THREED function, though I
have a feeling I will find it useful and it's been on my list of things
to do. But I use ARRAY.FILTER frequently. And I have built complex
averages of conditional events on auto-filtered ranges. (So THREED
really might make my life easier, if I could wrap my head around it.)

=dman=

P.S. By way of hairy example, this is a formula I use to tell me how
many stock trades per year I'm tracking in the auto-filtered
selection, for either buys or sells (swap the "<" for a "" to
do the buys):

=365.2425/((MAX(IF(ARRAY.FILTER(dataColG)0,ARRAY.FILTER(tra deTime)))-MIN(IF(ARRAY.FILTER(dataColG)0,ARRAY.FILTER(trade Time))))/(SUMPRODUCT(--(ARRAY.FILTER(dataColG)0))-1))

(It would be quite a bit longer if I weren't already using named formulas for dataColG and
tradeTime!)

The conditional MAX/MIN part came from help via this group the
other day. (Thanks again!)

===============
In , Chad spake thusly:
Biff,

That link doesn't seem to work anymore. Any chance you have another way I
could get a hold of the "Threed" UDF. That thing could solve a lot of
problems for me.

Thanks,
Chad


"T. Valko" wrote:

if there is a way to do a simple formula

Well, simple being relative! <g

Assuming the values to average are positive:

=SUM(Jan:Dec!CT19)/SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1:1 2"))*30,"mmm")&"!CT19"),"0"))

Or , if your OK with using an add-in...

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula:

=AVERAGE(IF(THREED(Jan:Dec!CT19),THREED(Jan:Dec!CT 19)))

--
Biff
Microsoft Excel MVP


"Ellen G" wrote in message
...
That would certainly work, however I'd love to know if there is a way to
do a
simple formula so I don't have to store in the information twice (once in
the
individual cells and once on another sheet).

Thanks for your help.

Ellen

"Ellen G" wrote:

Hi there --

I have a workbook with a tab for each month (i.e. Jan, Feb, Mar...Dec).

I need to AVERAGE CT19 in each. And, I don't want to include any that are
0.
So, I've tried the following, but it doesn't work. (I have tried this
both as
an array and not an array.)

=AVERAGE(IF(Jan:Dec!$CT19<0,Jan:Dec!$CT19)

Any suggestions?

Thanks so much.

Ellen

In , T. Valko
spake thusly:

Try this alternative site:

http://www.download.com/Morefunc/300...-10423159.html

Oh! That's also good -- and no registration required.

=dman=