Hello,

I'm currently using page 1 of a work book as a data base. Not certain thats
the best idea because opening, saving and calculating have become slower as
the information expands. This is not my real question, but any feed back will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like to
include matching cells from column H that meet criterian held in columns W,
X, Y and Z (1 each). then I would need to add and average these cells. Is
this possible to do in one step? Should I use a Index Match or would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in B1:B100 =
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not certain
thats
the best idea because opening, saving and calculating have become slower
as
the information expands. This is not my real question, but any feed back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like to
include matching cells from column H that meet criterian held in columns
W,
X, Y and Z (1 each). then I would need to add and average these cells. Is
this possible to do in one step? Should I use a Index Match or would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in B1:B100 =
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not certain
thats
the best idea because opening, saving and calculating have become slower
as
the information expands. This is not my real question, but any feed back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like to
include matching cells from column H that meet criterian held in columns
W,
X, Y and Z (1 each). then I would need to add and average these cells. Is
this possible to do in one step? Should I use a Index Match or would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Are you really using the entire columns?

This formula will work but it will add to the calculation time that you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not certain
thats
the best idea because opening, saving and calculating have become
slower
as
the information expands. This is not my real question, but any feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like to
include matching cells from column H that meet criterian held in
columns
W,
X, Y and Z (1 each). then I would need to add and average these cells.
Is
this possible to do in one step? Should I use a Index Match or would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

The only reason I need to use the entire column is to update the average. If
it was possible to save the average and the count as to how many cells were
used to compile it, I could delete sheet1! and start fresh each time. This
would cut the rows down from 65535 to about 2500. I would certainly be
interested if this were possible. That would help with the speed problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time that you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not certain
thats
the best idea because opening, saving and calculating have become
slower
as
the information expands. This is not my real question, but any feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like to
include matching cells from column H that meet criterian held in
columns
W,
X, Y and Z (1 each). then I would need to add and average these cells.
Is
this possible to do in one step? Should I use a Index Match or would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update the average.
If
it was possible to save the average and the count as to how many cells
were
used to compile it, I could delete sheet1! and start fresh each time. This
would cut the rows down from 65535 to about 2500. I would certainly be
interested if this were possible. That would help with the speed problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time that you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not
certain
thats
the best idea because opening, saving and calculating have become
slower
as
the information expands. This is not my real question, but any feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like
to
include matching cells from column H that meet criterian held in
columns
W,
X, Y and Z (1 each). then I would need to add and average these
cells.
Is
this possible to do in one step? Should I use a Index Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Thanks for the link, but what is a dynamic Range and what will it do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update the average.
If
it was possible to save the average and the count as to how many cells
were
used to compile it, I could delete sheet1! and start fresh each time. This
would cut the rows down from 65535 to about 2500. I would certainly be
interested if this were possible. That would help with the speed problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time that you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not
certain
thats
the best idea because opening, saving and calculating have become
slower
as
the information expands. This is not my real question, but any feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would like
to
include matching cells from column H that meet criterian held in
columns
W,
X, Y and Z (1 each). then I would need to add and average these
cells.
Is
this possible to do in one step? Should I use a Index Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

A dynamic range is a range reference that adjusts its size automatically as
you add or remove data from the range.

For example, in your situation it sounds like you're using a range like
A2:A65535 just because you may not how much data is actually in that range
and by using (almost) the entire column as a reference you'll be guaranteed
to cover the range. That does work but in using the type of array formula
that you're using this is very inefficient because *every* cell in the
referenced range is calculated. So, if you have 60,000 empty cells in that
range you're wasting resources by having to evaluate every one of those
empty cells. It's things like this that add to slow calculation.

Assume in your example that there are actually 5000 rows of real data. In
your formula you're testing 5 columns. So if you have 60,000 empty cells per
column you're wasting resources by having to test 300,000 empty cells.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for the link, but what is a dynamic Range and what will it do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update the
average.
If
it was possible to save the average and the count as to how many cells
were
used to compile it, I could delete sheet1! and start fresh each time.
This
would cut the rows down from 65535 to about 2500. I would certainly be
interested if this were possible. That would help with the speed
problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time that
you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not
certain
thats
the best idea because opening, saving and calculating have become
slower
as
the information expands. This is not my real question, but any
feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would
like
to
include matching cells from column H that meet criterian held in
columns
W,
X, Y and Z (1 each). then I would need to add and average these
cells.
Is
this possible to do in one step? Should I use a Index Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

So the dynamic range adjusts the calculated cells as the range grows or
shrinks. Do the calculations need to be adjusted, or is A2:A65535 viable?
Also will it effect rows as well, or just columns?

"T. Valko" wrote:

A dynamic range is a range reference that adjusts its size automatically as
you add or remove data from the range.

For example, in your situation it sounds like you're using a range like
A2:A65535 just because you may not how much data is actually in that range
and by using (almost) the entire column as a reference you'll be guaranteed
to cover the range. That does work but in using the type of array formula
that you're using this is very inefficient because *every* cell in the
referenced range is calculated. So, if you have 60,000 empty cells in that
range you're wasting resources by having to evaluate every one of those
empty cells. It's things like this that add to slow calculation.

Assume in your example that there are actually 5000 rows of real data. In
your formula you're testing 5 columns. So if you have 60,000 empty cells per
column you're wasting resources by having to test 300,000 empty cells.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for the link, but what is a dynamic Range and what will it do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update the
average.
If
it was possible to save the average and the count as to how many cells
were
used to compile it, I could delete sheet1! and start fresh each time.
This
would cut the rows down from 65535 to about 2500. I would certainly be
interested if this were possible. That would help with the speed
problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time that
you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not
certain
thats
the best idea because opening, saving and calculating have become
slower
as
the information expands. This is not my real question, but any
feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would
like
to
include matching cells from column H that meet criterian held in
columns
W,
X, Y and Z (1 each). then I would need to add and average these
cells.
Is
this possible to do in one step? Should I use a Index Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Hi

Try creating 5 named ranges
InsertNameDefine Wrng Refers to. =OFFSET($W$2,0,0,COUNTA($W:$W))
Repeat for Xrng, Yrng, Zrng and Hrng
I would keep the COUNTA part consistent upon just the one column, as
this will guarantee that all ranges are of the same length. Choose the
column which has the maximum amount of data.

Then the Array formula becomes
{=AVERAGE(IF((Wrng="Red")*(Xrng="Blue")*(Yrng="Yel low")*(Zrng="Green"),Hrng))}
and will only calculate based upon the number of rows with data
--
Regards

Roger Govier


"M.A.Tyler" <Great Lakes State wrote in message
...
So the dynamic range adjusts the calculated cells as the range grows
or
shrinks. Do the calculations need to be adjusted, or is A2:A65535
viable?
Also will it effect rows as well, or just columns?

"T. Valko" wrote:

A dynamic range is a range reference that adjusts its size
automatically as
you add or remove data from the range.

For example, in your situation it sounds like you're using a range
like
A2:A65535 just because you may not how much data is actually in that
range
and by using (almost) the entire column as a reference you'll be
guaranteed
to cover the range. That does work but in using the type of array
formula
that you're using this is very inefficient because *every* cell in
the
referenced range is calculated. So, if you have 60,000 empty cells in
that
range you're wasting resources by having to evaluate every one of
those
empty cells. It's things like this that add to slow calculation.

Assume in your example that there are actually 5000 rows of real
data. In
your formula you're testing 5 columns. So if you have 60,000 empty
cells per
column you're wasting resources by having to test 300,000 empty
cells.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for the link, but what is a dynamic Range and what will it
do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update the
average.
If
it was possible to save the average and the count as to how many
cells
were
used to compile it, I could delete sheet1! and start fresh each
time.
This
would cut the rows down from 65535 to about 2500. I would
certainly be
interested if this were possible. That would help with the speed
problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time
that
you
already mentioned is an issue:

Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding
cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base.
Not
certain
thats
the best idea because opening, saving and calculating have
become
slower
as
the information expands. This is not my real question, but
any
feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations.
Would
like
to
include matching cells from column H that meet criterian
held in
columns
W,
X, Y and Z (1 each). then I would need to add and average
these
cells.
Is
this possible to do in one step? Should I use a Index
Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

=OFFSET($W$2,0,0,COUNTA($W:$W))

For the OP....

You may be wondering why we use the entire column reference in the Counta
function in the above formula but I just told you not to use entire columns
in my other reply.

The reason is that these are entirely different formulas. An array formula
will evaluate *every* cell referenced. In the formula above, the Counta
function does not evaluate every cell referenced. It will only evaluate the
used range. The used range is the grid of cells that actually have something
in them and is measured left to right, top to bottom. For example, if you
open a fresh workbook and on sheet1 enter something in cell D5 that sets the
used range to A1:D5. If you entered this formula in cell A1:

=COUNTA(D:D)

The formula will only evaluate D1:D5

Biff

"Roger Govier" wrote in message
...
Hi

Try creating 5 named ranges
InsertNameDefine Wrng Refers to. =OFFSET($W$2,0,0,COUNTA($W:$W))
Repeat for Xrng, Yrng, Zrng and Hrng
I would keep the COUNTA part consistent upon just the one column, as this
will guarantee that all ranges are of the same length. Choose the column
which has the maximum amount of data.

Then the Array formula becomes
{=AVERAGE(IF((Wrng="Red")*(Xrng="Blue")*(Yrng="Yel low")*(Zrng="Green"),Hrng))}
and will only calculate based upon the number of rows with data
--
Regards

Roger Govier


"M.A.Tyler" <Great Lakes State wrote in message
...
So the dynamic range adjusts the calculated cells as the range grows or
shrinks. Do the calculations need to be adjusted, or is A2:A65535 viable?
Also will it effect rows as well, or just columns?

"T. Valko" wrote:

A dynamic range is a range reference that adjusts its size automatically
as
you add or remove data from the range.

For example, in your situation it sounds like you're using a range like
A2:A65535 just because you may not how much data is actually in that
range
and by using (almost) the entire column as a reference you'll be
guaranteed
to cover the range. That does work but in using the type of array
formula
that you're using this is very inefficient because *every* cell in the
referenced range is calculated. So, if you have 60,000 empty cells in
that
range you're wasting resources by having to evaluate every one of those
empty cells. It's things like this that add to slow calculation.

Assume in your example that there are actually 5000 rows of real data.
In
your formula you're testing 5 columns. So if you have 60,000 empty cells
per
column you're wasting resources by having to test 300,000 empty cells.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for the link, but what is a dynamic Range and what will it do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update the
average.
If
it was possible to save the average and the count as to how many
cells
were
used to compile it, I could delete sheet1! and start fresh each
time.
This
would cut the rows down from 65535 to about 2500. I would certainly
be
interested if this were possible. That would help with the speed
problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation time
that
you
already mentioned is an issue:

Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding cells
in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data base. Not
certain
thats
the best idea because opening, saving and calculating have
become
slower
as
the information expands. This is not my real question, but
any
feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations. Would
like
to
include matching cells from column H that meet criterian held
in
columns
W,
X, Y and Z (1 each). then I would need to add and average
these
cells.
Is
this possible to do in one step? Should I use a Index Match
or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Thanks Biff, for adding that clarification.
I can see it would have seemed a little contradictory to the OP.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
=OFFSET($W$2,0,0,COUNTA($W:$W))

For the OP....

You may be wondering why we use the entire column reference in the
Counta function in the above formula but I just told you not to use
entire columns in my other reply.

The reason is that these are entirely different formulas. An array
formula will evaluate *every* cell referenced. In the formula above,
the Counta function does not evaluate every cell referenced. It will
only evaluate the used range. The used range is the grid of cells that
actually have something in them and is measured left to right, top to
bottom. For example, if you open a fresh workbook and on sheet1 enter
something in cell D5 that sets the used range to A1:D5. If you entered
this formula in cell A1:

=COUNTA(D:D)

The formula will only evaluate D1:D5

Biff

"Roger Govier" wrote in message
...
Hi

Try creating 5 named ranges
InsertNameDefine Wrng Refers to.
=OFFSET($W$2,0,0,COUNTA($W:$W))
Repeat for Xrng, Yrng, Zrng and Hrng
I would keep the COUNTA part consistent upon just the one column, as
this will guarantee that all ranges are of the same length. Choose
the column which has the maximum amount of data.

Then the Array formula becomes
{=AVERAGE(IF((Wrng="Red")*(Xrng="Blue")*(Yrng="Yel low")*(Zrng="Green"),Hrng))}
and will only calculate based upon the number of rows with data
--
Regards

Roger Govier


"M.A.Tyler" <Great Lakes State wrote in message
...
So the dynamic range adjusts the calculated cells as the range grows
or
shrinks. Do the calculations need to be adjusted, or is A2:A65535
viable?
Also will it effect rows as well, or just columns?

"T. Valko" wrote:

A dynamic range is a range reference that adjusts its size
automatically as
you add or remove data from the range.

For example, in your situation it sounds like you're using a range
like
A2:A65535 just because you may not how much data is actually in
that range
and by using (almost) the entire column as a reference you'll be
guaranteed
to cover the range. That does work but in using the type of array
formula
that you're using this is very inefficient because *every* cell in
the
referenced range is calculated. So, if you have 60,000 empty cells
in that
range you're wasting resources by having to evaluate every one of
those
empty cells. It's things like this that add to slow calculation.

Assume in your example that there are actually 5000 rows of real
data. In
your formula you're testing 5 columns. So if you have 60,000 empty
cells per
column you're wasting resources by having to test 300,000 empty
cells.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for the link, but what is a dynamic Range and what will it
do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update
the
average.
If
it was possible to save the average and the count as to how
many cells
were
used to compile it, I could delete sheet1! and start fresh
each time.
This
would cut the rows down from 65535 to about 2500. I would
certainly be
interested if this were possible. That would help with the
speed
problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation
time that
you
already mentioned is an issue:

Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells
in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding
cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data
base. Not
certain
thats
the best idea because opening, saving and calculating
have become
slower
as
the information expands. This is not my real question,
but any
feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations.
Would
like
to
include matching cells from column H that meet criterian
held in
columns
W,
X, Y and Z (1 each). then I would need to add and
average these
cells.
Is
this possible to do in one step? Should I use a Index
Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler

Thank you both for your help it's working great!

"Roger Govier" wrote:

Thanks Biff, for adding that clarification.
I can see it would have seemed a little contradictory to the OP.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
=OFFSET($W$2,0,0,COUNTA($W:$W))

For the OP....

You may be wondering why we use the entire column reference in the
Counta function in the above formula but I just told you not to use
entire columns in my other reply.

The reason is that these are entirely different formulas. An array
formula will evaluate *every* cell referenced. In the formula above,
the Counta function does not evaluate every cell referenced. It will
only evaluate the used range. The used range is the grid of cells that
actually have something in them and is measured left to right, top to
bottom. For example, if you open a fresh workbook and on sheet1 enter
something in cell D5 that sets the used range to A1:D5. If you entered
this formula in cell A1:

=COUNTA(D:D)

The formula will only evaluate D1:D5

Biff

"Roger Govier" wrote in message
...
Hi

Try creating 5 named ranges
InsertNameDefine Wrng Refers to.
=OFFSET($W$2,0,0,COUNTA($W:$W))
Repeat for Xrng, Yrng, Zrng and Hrng
I would keep the COUNTA part consistent upon just the one column, as
this will guarantee that all ranges are of the same length. Choose
the column which has the maximum amount of data.

Then the Array formula becomes
{=AVERAGE(IF((Wrng="Red")*(Xrng="Blue")*(Yrng="Yel low")*(Zrng="Green"),Hrng))}
and will only calculate based upon the number of rows with data
--
Regards

Roger Govier


"M.A.Tyler" <Great Lakes State wrote in message
...
So the dynamic range adjusts the calculated cells as the range grows
or
shrinks. Do the calculations need to be adjusted, or is A2:A65535
viable?
Also will it effect rows as well, or just columns?

"T. Valko" wrote:

A dynamic range is a range reference that adjusts its size
automatically as
you add or remove data from the range.

For example, in your situation it sounds like you're using a range
like
A2:A65535 just because you may not how much data is actually in
that range
and by using (almost) the entire column as a reference you'll be
guaranteed
to cover the range. That does work but in using the type of array
formula
that you're using this is very inefficient because *every* cell in
the
referenced range is calculated. So, if you have 60,000 empty cells
in that
range you're wasting resources by having to evaluate every one of
those
empty cells. It's things like this that add to slow calculation.

Assume in your example that there are actually 5000 rows of real
data. In
your formula you're testing 5 columns. So if you have 60,000 empty
cells per
column you're wasting resources by having to test 300,000 empty
cells.

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Thanks for the link, but what is a dynamic Range and what will it
do?

"T. Valko" wrote:

It sounds like what you need to do is to use a dynamic range.

See this:

http://contextures.com/xlNames01.html#Dynamic

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
The only reason I need to use the entire column is to update
the
average.
If
it was possible to save the average and the count as to how
many cells
were
used to compile it, I could delete sheet1! and start fresh
each time.
This
would cut the rows down from 65535 to about 2500. I would
certainly be
interested if this were possible. That would help with the
speed
problem!

Thank you for your help and the formula.

Best regards,

M.A.Tyler

"T. Valko" wrote:

Are you really using the entire columns?

This formula will work but it will add to the calculation
time that
you
already mentioned is an issue:

Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF((W2:W65535="Red")*(X2:X65535="Blue")*( Y2:Y65535="Yellow")*(Z2:Z65535="Green"),H2:H65535) )

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Need to Average cells H2:H65535, where corresponding cells
in
W2:W65535="Red", X2:X65535="Blue", Y2:Y65535="Yellow" and
Z2:Z65535="Green".
Need to enter the formulas in Sheet2!S2:S100.

"T. Valko" wrote:

Try rephrasing your question in terms like this:

I need to average cells A1:A100 where the corresponding
cells in
B1:B100
=
"this", C1:C100 = "this", D1:D100 = "this".

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
Hello,

I'm currently using page 1 of a work book as a data
base. Not
certain
thats
the best idea because opening, saving and calculating
have become
slower
as
the information expands. This is not my real question,
but any
feed
back
will
be welcome.

So page 1 is the database, on page 2 some calculations.
Would
like
to
include matching cells from column H that meet criterian
held in
columns
W,
X, Y and Z (1 each). then I would need to add and
average these
cells.
Is
this possible to do in one step? Should I use a Index
Match or
would a
filter be the way.

Thanks, as always for the help!

M.A.Tyler