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

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

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

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

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

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

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