repeating columnar data
 

I use multiple columns per month and would like to do an average (and other
functions) on past months to forcast a rate for future months. Since the
columns that I want to average are not adjacent, I cannot find a way to do
it. Is there a function to identify the columns similar to the sumif
function, where I can identify the column based on a heading, and then
average (or stddev, variance) them. Another approach would be to index into
or offset or indirect into the table selecting every fifth column ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average (and other
functions) on past months to forcast a rate for future months. Since the
columns that I want to average are not adjacent, I cannot find a way to do
it. Is there a function to identify the columns similar to the sumif
function, where I can identify the column based on a heading, and then
average (or stddev, variance) them. Another approach would be to index
into
or offset or indirect into the table selecting every fifth column ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

Thanks Biff, after I hit post, I realized that I should have given more
information. That was an example. I use most of the 256 columns frequently.
It is monthly projection data. The Heading option actually works better
because of the unfixed columns (months can be added at the end of the sheet
without having to change any parameters). An additional problem is that for
an average of variable columns due to some tasks starting on different
months, a blank in the column would not be included in the COUNT and a zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average (and other
functions) on past months to forcast a rate for future months. Since the
columns that I want to average are not adjacent, I cannot find a way to do
it. Is there a function to identify the columns similar to the sumif
function, where I can identify the column based on a heading, and then
average (or stddev, variance) them. Another approach would be to index
into
or offset or indirect into the table selecting every fifth column ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))

To exclude cells that contain 0:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<0 ),B14:Q14))

Biff

"jack" wrote in message
...
Thanks Biff, after I hit post, I realized that I should have given more
information. That was an example. I use most of the 256 columns
frequently.
It is monthly projection data. The Heading option actually works better
because of the unfixed columns (months can be added at the end of the
sheet
without having to change any parameters). An additional problem is that
for
an average of variable columns due to some tasks starting on different
months, a blank in the column would not be included in the COUNT and a
zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average (and
other
functions) on past months to forcast a rate for future months. Since
the
columns that I want to average are not adjacent, I cannot find a way to
do
it. Is there a function to identify the columns similar to the sumif
function, where I can identify the column based on a heading, and then
average (or stddev, variance) them. Another approach would be to index
into
or offset or indirect into the table selecting every fifth column ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

Biff, I thought you had it. It seems that when the mod=2 is true, it
includes all the cells in the average b14,c14,d14,...,q14. The average does
not skip the cells where the mod=2 is false. Also the ideal would be to
include the cells with 0 or a number and exclude the blank cells. I could
proabably work that part out.

"Biff" wrote:

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))

To exclude cells that contain 0:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<0 ),B14:Q14))

Biff

"jack" wrote in message
...
Thanks Biff, after I hit post, I realized that I should have given more
information. That was an example. I use most of the 256 columns
frequently.
It is monthly projection data. The Heading option actually works better
because of the unfixed columns (months can be added at the end of the
sheet
without having to change any parameters). An additional problem is that
for
an average of variable columns due to some tasks starting on different
months, a blank in the column would not be included in the COUNT and a
zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average (and
other
functions) on past months to forcast a rate for future months. Since
the
columns that I want to average are not adjacent, I cannot find a way to
do
it. Is there a function to identify the columns similar to the sumif
function, where I can identify the column based on a heading, and then
average (or stddev, variance) them. Another approach would be to index
into
or offset or indirect into the table selecting every fifth column ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

The formula is an array formula. If you don't enter it as an array it won't
work properly.

To enter an array formula:

Type the formula in the cell then, instead of hitting the ENTER key like you
normally would you MUST use a combination of keys. Hold down both the CTRL
key and the SHIFT key then hit ENTER. If done properly Excel will enclose
the formula in squiggly braces { }. You cannot just type these braces in,
you MUST use the key combo. Also, if you edit an array formula it must be
re-entered as an array using the key combo.

OK, to include cells with zero and exclude blank cells:

Array entered:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<" "),B14:Q14))

Biff

"jack" wrote in message
...
Biff, I thought you had it. It seems that when the mod=2 is true, it
includes all the cells in the average b14,c14,d14,...,q14. The average
does
not skip the cells where the mod=2 is false. Also the ideal would be to
include the cells with 0 or a number and exclude the blank cells. I could
proabably work that part out.

"Biff" wrote:

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))

To exclude cells that contain 0:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<0 ),B14:Q14))

Biff

"jack" wrote in message
...
Thanks Biff, after I hit post, I realized that I should have given more
information. That was an example. I use most of the 256 columns
frequently.
It is monthly projection data. The Heading option actually works
better
because of the unfixed columns (months can be added at the end of the
sheet
without having to change any parameters). An additional problem is
that
for
an average of variable columns due to some tasks starting on different
months, a blank in the column would not be included in the COUNT and a
zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average (and
other
functions) on past months to forcast a rate for future months.
Since
the
columns that I want to average are not adjacent, I cannot find a way
to
do
it. Is there a function to identify the columns similar to the
sumif
function, where I can identify the column based on a heading, and
then
average (or stddev, variance) them. Another approach would be to
index
into
or offset or indirect into the table selecting every fifth column
ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

OK, thanks after I did the post I continued to work with it an I finally got
the CTRL SHIFT ENTER. I did not understand how to use that. I solved it
with an ISNUMBER function. It took me awhile because I was trying to do an
IF(AND(cond1,cond2),result) but it wouldn't work. Do you understand why the
times (*) between the two logical statements cond1*cond2?

"Biff" wrote:

The formula is an array formula. If you don't enter it as an array it won't
work properly.

To enter an array formula:

Type the formula in the cell then, instead of hitting the ENTER key like you
normally would you MUST use a combination of keys. Hold down both the CTRL
key and the SHIFT key then hit ENTER. If done properly Excel will enclose
the formula in squiggly braces { }. You cannot just type these braces in,
you MUST use the key combo. Also, if you edit an array formula it must be
re-entered as an array using the key combo.

OK, to include cells with zero and exclude blank cells:

Array entered:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<" "),B14:Q14))

Biff

"jack" wrote in message
...
Biff, I thought you had it. It seems that when the mod=2 is true, it
includes all the cells in the average b14,c14,d14,...,q14. The average
does
not skip the cells where the mod=2 is false. Also the ideal would be to
include the cells with 0 or a number and exclude the blank cells. I could
proabably work that part out.

"Biff" wrote:

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))

To exclude cells that contain 0:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<0 ),B14:Q14))

Biff

"jack" wrote in message
...
Thanks Biff, after I hit post, I realized that I should have given more
information. That was an example. I use most of the 256 columns
frequently.
It is monthly projection data. The Heading option actually works
better
because of the unfixed columns (months can be added at the end of the
sheet
without having to change any parameters). An additional problem is
that
for
an average of variable columns due to some tasks starting on different
months, a blank in the column would not be included in the COUNT and a
zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average (and
other
functions) on past months to forcast a rate for future months.
Since
the
columns that I want to average are not adjacent, I cannot find a way
to
do
it. Is there a function to identify the columns similar to the
sumif
function, where I can identify the column based on a heading, and
then
average (or stddev, variance) them. Another approach would be to
index
into
or offset or indirect into the table selecting every fifth column
ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

Do you understand why the
times (*) between the two logical statements cond1*cond2?

I'm guessing that what you're asking is why the above works and why the use
of AND didn't work?

When you use AND every argument must evaluate to TRUE and if an argument is
an array then every element of the array must also evaluate to TRUE.

Multiplying the arrays together only requires that the individual elements
compared to each other evaluate to TRUE.

Biff

"jack" wrote in message
...
OK, thanks after I did the post I continued to work with it an I finally
got
the CTRL SHIFT ENTER. I did not understand how to use that. I solved it
with an ISNUMBER function. It took me awhile because I was trying to do
an
IF(AND(cond1,cond2),result) but it wouldn't work. Do you understand why
the
times (*) between the two logical statements cond1*cond2?

"Biff" wrote:

The formula is an array formula. If you don't enter it as an array it
won't
work properly.

To enter an array formula:

Type the formula in the cell then, instead of hitting the ENTER key like
you
normally would you MUST use a combination of keys. Hold down both the
CTRL
key and the SHIFT key then hit ENTER. If done properly Excel will enclose
the formula in squiggly braces { }. You cannot just type these braces in,
you MUST use the key combo. Also, if you edit an array formula it must be
re-entered as an array using the key combo.

OK, to include cells with zero and exclude blank cells:

Array entered:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<" "),B14:Q14))

Biff

"jack" wrote in message
...
Biff, I thought you had it. It seems that when the mod=2 is true, it
includes all the cells in the average b14,c14,d14,...,q14. The average
does
not skip the cells where the mod=2 is false. Also the ideal would be
to
include the cells with 0 or a number and exclude the blank cells. I
could
proabably work that part out.

"Biff" wrote:

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))

To exclude cells that contain 0:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<0 ),B14:Q14))

Biff

"jack" wrote in message
...
Thanks Biff, after I hit post, I realized that I should have given
more
information. That was an example. I use most of the 256 columns
frequently.
It is monthly projection data. The Heading option actually works
better
because of the unfixed columns (months can be added at the end of
the
sheet
without having to change any parameters). An additional problem is
that
for
an average of variable columns due to some tasks starting on
different
months, a blank in the column would not be included in the COUNT and
a
zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average
(and
other
functions) on past months to forcast a rate for future months.
Since
the
columns that I want to average are not adjacent, I cannot find a
way
to
do
it. Is there a function to identify the columns similar to the
sumif
function, where I can identify the column based on a heading, and
then
average (or stddev, variance) them. Another approach would be to
index
into
or offset or indirect into the table selecting every fifth column
ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

Biff, I really appreciate your help. I was off reworking my spreadsheet. I
would not have understood the array - CTRL SHIFT ENTER functionality and the
multiply vs. AND seems a bit convoluted. Perhaps more use and testing will
strengthen my understanding of both concepts. This array functionality is
something that I thought should be available but I could not find it or
understand how to use it via the HELP menu. Again, thank-you for your help
and time!
Jack Miller

"Biff" wrote:

Do you understand why the
times (*) between the two logical statements cond1*cond2?

I'm guessing that what you're asking is why the above works and why the use
of AND didn't work?

When you use AND every argument must evaluate to TRUE and if an argument is
an array then every element of the array must also evaluate to TRUE.

Multiplying the arrays together only requires that the individual elements
compared to each other evaluate to TRUE.

Biff

"jack" wrote in message
...
OK, thanks after I did the post I continued to work with it an I finally
got
the CTRL SHIFT ENTER. I did not understand how to use that. I solved it
with an ISNUMBER function. It took me awhile because I was trying to do
an
IF(AND(cond1,cond2),result) but it wouldn't work. Do you understand why
the
times (*) between the two logical statements cond1*cond2?

"Biff" wrote:

The formula is an array formula. If you don't enter it as an array it
won't
work properly.

To enter an array formula:

Type the formula in the cell then, instead of hitting the ENTER key like
you
normally would you MUST use a combination of keys. Hold down both the
CTRL
key and the SHIFT key then hit ENTER. If done properly Excel will enclose
the formula in squiggly braces { }. You cannot just type these braces in,
you MUST use the key combo. Also, if you edit an array formula it must be
re-entered as an array using the key combo.

OK, to include cells with zero and exclude blank cells:

Array entered:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<" "),B14:Q14))

Biff

"jack" wrote in message
...
Biff, I thought you had it. It seems that when the mod=2 is true, it
includes all the cells in the average b14,c14,d14,...,q14. The average
does
not skip the cells where the mod=2 is false. Also the ideal would be
to
include the cells with 0 or a number and exclude the blank cells. I
could
proabably work that part out.

"Biff" wrote:

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))

To exclude cells that contain 0:

=AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14<0 ),B14:Q14))

Biff

"jack" wrote in message
...
Thanks Biff, after I hit post, I realized that I should have given
more
information. That was an example. I use most of the 256 columns
frequently.
It is monthly projection data. The Heading option actually works
better
because of the unfixed columns (months can be added at the end of
the
sheet
without having to change any parameters). An additional problem is
that
for
an average of variable columns due to some tasks starting on
different
months, a blank in the column would not be included in the COUNT and
a
zero
would be included in the COUNT. ie. average = (sum of data) / COUNT

"Biff" wrote:

Hi!

If you only have 4 cells involved, what's wrong with:

=AVERAGE(B14,G14,L14,Q14)

Biff

"jack" wrote in message
...
I use multiple columns per month and would like to do an average
(and
other
functions) on past months to forcast a rate for future months.
Since
the
columns that I want to average are not adjacent, I cannot find a
way
to
do
it. Is there a function to identify the columns similar to the
sumif
function, where I can identify the column based on a heading, and
then
average (or stddev, variance) them. Another approach would be to
index
into
or offset or indirect into the table selecting every fifth column
ie.
average(b14:q14,5) average the values at b14,g14,L14,q14

repeating columnar data
 

if i had a coulmn with a list 1 to 10 how would i go about having them as
only that input and if i entered a 1 in the 2 spot that it wouldnt let the 1
enter cause its already there

"jack" wrote:

I use multiple columns per month and would like to do an average (and other
functions) on past months to forcast a rate for future months. Since the
columns that I want to average are not adjacent, I cannot find a way to do
it. Is there a function to identify the columns similar to the sumif
function, where I can identify the column based on a heading, and then
average (or stddev, variance) them. Another approach would be to index into
or offset or indirect into the table selecting every fifth column ie.
average(b14:q14,5) average the values at b14,g14,L14,q14