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
quot;jackquot; gt; wrote in message
...
gt;I use multiple columns per month and would like to do an average (and other
gt; functions) on past months to forcast a rate for future months. Since the
gt; columns that I want to average are not adjacent, I cannot find a way to do
gt; it. Is there a function to identify the columns similar to the sumif
gt; function, where I can identify the column based on a heading, and then
gt; average (or stddev, variance) them. Another approach would be to index
gt; into
gt; or offset or indirect into the table selecting every fifth column ie.
gt; 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
quot;Biffquot; wrote:
gt; Hi!
gt;
gt; If you only have 4 cells involved, what's wrong with:
gt;
gt; =AVERAGE(B14,G14,L14,Q14)
gt;
gt; Biff
gt;
gt; quot;jackquot; gt; wrote in message
gt; ...
gt; gt;I use multiple columns per month and would like to do an average (and other
gt; gt; functions) on past months to forcast a rate for future months. Since the
gt; gt; columns that I want to average are not adjacent, I cannot find a way to do
gt; gt; it. Is there a function to identify the columns similar to the sumif
gt; gt; function, where I can identify the column based on a heading, and then
gt; gt; average (or stddev, variance) them. Another approach would be to index
gt; gt; into
gt; gt; or offset or indirect into the table selecting every fifth column ie.
gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt;
gt;
gt;
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:Q14lt;gt;0 ),B14:Q14))
Biff
quot;jackquot; gt; wrote in message
...
gt; Thanks Biff, after I hit post, I realized that I should have given more
gt; information. That was an example. I use most of the 256 columns
gt; frequently.
gt; It is monthly projection data. The Heading option actually works better
gt; because of the unfixed columns (months can be added at the end of the
gt; sheet
gt; without having to change any parameters). An additional problem is that
gt; for
gt; an average of variable columns due to some tasks starting on different
gt; months, a blank in the column would not be included in the COUNT and a
gt; zero
gt; would be included in the COUNT. ie. average = (sum of data) / COUNT
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; If you only have 4 cells involved, what's wrong with:
gt;gt;
gt;gt; =AVERAGE(B14,G14,L14,Q14)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;jackquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I use multiple columns per month and would like to do an average (and
gt;gt; gt;other
gt;gt; gt; functions) on past months to forcast a rate for future months. Since
gt;gt; gt; the
gt;gt; gt; columns that I want to average are not adjacent, I cannot find a way to
gt;gt; gt; do
gt;gt; gt; it. Is there a function to identify the columns similar to the sumif
gt;gt; gt; function, where I can identify the column based on a heading, and then
gt;gt; gt; average (or stddev, variance) them. Another approach would be to index
gt;gt; gt; into
gt;gt; gt; or offset or indirect into the table selecting every fifth column ie.
gt;gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt;gt;
gt;gt;
gt;gt;
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.
quot;Biffquot; wrote:
gt; Try this:
gt;
gt; Array entered using the key combo of CTRL,SHIFT,ENTER:
gt;
gt; =AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))
gt;
gt; To exclude cells that contain 0:
gt;
gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;0 ),B14:Q14))
gt;
gt; Biff
gt;
gt; quot;jackquot; gt; wrote in message
gt; ...
gt; gt; Thanks Biff, after I hit post, I realized that I should have given more
gt; gt; information. That was an example. I use most of the 256 columns
gt; gt; frequently.
gt; gt; It is monthly projection data. The Heading option actually works better
gt; gt; because of the unfixed columns (months can be added at the end of the
gt; gt; sheet
gt; gt; without having to change any parameters). An additional problem is that
gt; gt; for
gt; gt; an average of variable columns due to some tasks starting on different
gt; gt; months, a blank in the column would not be included in the COUNT and a
gt; gt; zero
gt; gt; would be included in the COUNT. ie. average = (sum of data) / COUNT
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; If you only have 4 cells involved, what's wrong with:
gt; gt;gt;
gt; gt;gt; =AVERAGE(B14,G14,L14,Q14)
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;jackquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I use multiple columns per month and would like to do an average (and
gt; gt;gt; gt;other
gt; gt;gt; gt; functions) on past months to forcast a rate for future months. Since
gt; gt;gt; gt; the
gt; gt;gt; gt; columns that I want to average are not adjacent, I cannot find a way to
gt; gt;gt; gt; do
gt; gt;gt; gt; it. Is there a function to identify the columns similar to the sumif
gt; gt;gt; gt; function, where I can identify the column based on a heading, and then
gt; gt;gt; gt; average (or stddev, variance) them. Another approach would be to index
gt; gt;gt; gt; into
gt; gt;gt; gt; or offset or indirect into the table selecting every fifth column ie.
gt; gt;gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
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:Q14lt;gt;quot; quot;),B14:Q14))
Biff
quot;jackquot; gt; wrote in message
...
gt; Biff, I thought you had it. It seems that when the mod=2 is true, it
gt; includes all the cells in the average b14,c14,d14,...,q14. The average
gt; does
gt; not skip the cells where the mod=2 is false. Also the ideal would be to
gt; include the cells with 0 or a number and exclude the blank cells. I could
gt; proabably work that part out.
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Try this:
gt;gt;
gt;gt; Array entered using the key combo of CTRL,SHIFT,ENTER:
gt;gt;
gt;gt; =AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))
gt;gt;
gt;gt; To exclude cells that contain 0:
gt;gt;
gt;gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;0 ),B14:Q14))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;jackquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Thanks Biff, after I hit post, I realized that I should have given more
gt;gt; gt; information. That was an example. I use most of the 256 columns
gt;gt; gt; frequently.
gt;gt; gt; It is monthly projection data. The Heading option actually works
gt;gt; gt; better
gt;gt; gt; because of the unfixed columns (months can be added at the end of the
gt;gt; gt; sheet
gt;gt; gt; without having to change any parameters). An additional problem is
gt;gt; gt; that
gt;gt; gt; for
gt;gt; gt; an average of variable columns due to some tasks starting on different
gt;gt; gt; months, a blank in the column would not be included in the COUNT and a
gt;gt; gt; zero
gt;gt; gt; would be included in the COUNT. ie. average = (sum of data) / COUNT
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; If you only have 4 cells involved, what's wrong with:
gt;gt; gt;gt;
gt;gt; gt;gt; =AVERAGE(B14,G14,L14,Q14)
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;jackquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I use multiple columns per month and would like to do an average (and
gt;gt; gt;gt; gt;other
gt;gt; gt;gt; gt; functions) on past months to forcast a rate for future months.
gt;gt; gt;gt; gt; Since
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; columns that I want to average are not adjacent, I cannot find a way
gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt; do
gt;gt; gt;gt; gt; it. Is there a function to identify the columns similar to the
gt;gt; gt;gt; gt; sumif
gt;gt; gt;gt; gt; function, where I can identify the column based on a heading, and
gt;gt; gt;gt; gt; then
gt;gt; gt;gt; gt; average (or stddev, variance) them. Another approach would be to
gt;gt; gt;gt; gt; index
gt;gt; gt;gt; gt; into
gt;gt; gt;gt; gt; or offset or indirect into the table selecting every fifth column
gt;gt; gt;gt; gt; ie.
gt;gt; gt;gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
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?
quot;Biffquot; wrote:
gt; The formula is an array formula. If you don't enter it as an array it won't
gt; work properly.
gt;
gt; To enter an array formula:
gt;
gt; Type the formula in the cell then, instead of hitting the ENTER key like you
gt; normally would you MUST use a combination of keys. Hold down both the CTRL
gt; key and the SHIFT key then hit ENTER. If done properly Excel will enclose
gt; the formula in squiggly braces { }. You cannot just type these braces in,
gt; you MUST use the key combo. Also, if you edit an array formula it must be
gt; re-entered as an array using the key combo.
gt;
gt; OK, to include cells with zero and exclude blank cells:
gt;
gt; Array entered:
gt;
gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;quot; quot;),B14:Q14))
gt;
gt; Biff
gt;
gt; quot;jackquot; gt; wrote in message
gt; ...
gt; gt; Biff, I thought you had it. It seems that when the mod=2 is true, it
gt; gt; includes all the cells in the average b14,c14,d14,...,q14. The average
gt; gt; does
gt; gt; not skip the cells where the mod=2 is false. Also the ideal would be to
gt; gt; include the cells with 0 or a number and exclude the blank cells. I could
gt; gt; proabably work that part out.
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Try this:
gt; gt;gt;
gt; gt;gt; Array entered using the key combo of CTRL,SHIFT,ENTER:
gt; gt;gt;
gt; gt;gt; =AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))
gt; gt;gt;
gt; gt;gt; To exclude cells that contain 0:
gt; gt;gt;
gt; gt;gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;0 ),B14:Q14))
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;jackquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Thanks Biff, after I hit post, I realized that I should have given more
gt; gt;gt; gt; information. That was an example. I use most of the 256 columns
gt; gt;gt; gt; frequently.
gt; gt;gt; gt; It is monthly projection data. The Heading option actually works
gt; gt;gt; gt; better
gt; gt;gt; gt; because of the unfixed columns (months can be added at the end of the
gt; gt;gt; gt; sheet
gt; gt;gt; gt; without having to change any parameters). An additional problem is
gt; gt;gt; gt; that
gt; gt;gt; gt; for
gt; gt;gt; gt; an average of variable columns due to some tasks starting on different
gt; gt;gt; gt; months, a blank in the column would not be included in the COUNT and a
gt; gt;gt; gt; zero
gt; gt;gt; gt; would be included in the COUNT. ie. average = (sum of data) / COUNT
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Biffquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Hi!
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; If you only have 4 cells involved, what's wrong with:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =AVERAGE(B14,G14,L14,Q14)
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Biff
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;jackquot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt;I use multiple columns per month and would like to do an average (and
gt; gt;gt; gt;gt; gt;other
gt; gt;gt; gt;gt; gt; functions) on past months to forcast a rate for future months.
gt; gt;gt; gt;gt; gt; Since
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; columns that I want to average are not adjacent, I cannot find a way
gt; gt;gt; gt;gt; gt; to
gt; gt;gt; gt;gt; gt; do
gt; gt;gt; gt;gt; gt; it. Is there a function to identify the columns similar to the
gt; gt;gt; gt;gt; gt; sumif
gt; gt;gt; gt;gt; gt; function, where I can identify the column based on a heading, and
gt; gt;gt; gt;gt; gt; then
gt; gt;gt; gt;gt; gt; average (or stddev, variance) them. Another approach would be to
gt; gt;gt; gt;gt; gt; index
gt; gt;gt; gt;gt; gt; into
gt; gt;gt; gt;gt; gt; or offset or indirect into the table selecting every fifth column
gt; gt;gt; gt;gt; gt; ie.
gt; gt;gt; gt;gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
gt;Do you understand why the
gt;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
quot;jackquot; gt; wrote in message
...
gt; OK, thanks after I did the post I continued to work with it an I finally
gt; got
gt; the CTRL SHIFT ENTER. I did not understand how to use that. I solved it
gt; with an ISNUMBER function. It took me awhile because I was trying to do
gt; an
gt; IF(AND(cond1,cond2),result) but it wouldn't work. Do you understand why
gt; the
gt; times (*) between the two logical statements cond1*cond2?
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; The formula is an array formula. If you don't enter it as an array it
gt;gt; won't
gt;gt; work properly.
gt;gt;
gt;gt; To enter an array formula:
gt;gt;
gt;gt; Type the formula in the cell then, instead of hitting the ENTER key like
gt;gt; you
gt;gt; normally would you MUST use a combination of keys. Hold down both the
gt;gt; CTRL
gt;gt; key and the SHIFT key then hit ENTER. If done properly Excel will enclose
gt;gt; the formula in squiggly braces { }. You cannot just type these braces in,
gt;gt; you MUST use the key combo. Also, if you edit an array formula it must be
gt;gt; re-entered as an array using the key combo.
gt;gt;
gt;gt; OK, to include cells with zero and exclude blank cells:
gt;gt;
gt;gt; Array entered:
gt;gt;
gt;gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;quot; quot;),B14:Q14))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;jackquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Biff, I thought you had it. It seems that when the mod=2 is true, it
gt;gt; gt; includes all the cells in the average b14,c14,d14,...,q14. The average
gt;gt; gt; does
gt;gt; gt; not skip the cells where the mod=2 is false. Also the ideal would be
gt;gt; gt; to
gt;gt; gt; include the cells with 0 or a number and exclude the blank cells. I
gt;gt; gt; could
gt;gt; gt; proabably work that part out.
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Try this:
gt;gt; gt;gt;
gt;gt; gt;gt; Array entered using the key combo of CTRL,SHIFT,ENTER:
gt;gt; gt;gt;
gt;gt; gt;gt; =AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))
gt;gt; gt;gt;
gt;gt; gt;gt; To exclude cells that contain 0:
gt;gt; gt;gt;
gt;gt; gt;gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;0 ),B14:Q14))
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;jackquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; Thanks Biff, after I hit post, I realized that I should have given
gt;gt; gt;gt; gt; more
gt;gt; gt;gt; gt; information. That was an example. I use most of the 256 columns
gt;gt; gt;gt; gt; frequently.
gt;gt; gt;gt; gt; It is monthly projection data. The Heading option actually works
gt;gt; gt;gt; gt; better
gt;gt; gt;gt; gt; because of the unfixed columns (months can be added at the end of
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; sheet
gt;gt; gt;gt; gt; without having to change any parameters). An additional problem is
gt;gt; gt;gt; gt; that
gt;gt; gt;gt; gt; for
gt;gt; gt;gt; gt; an average of variable columns due to some tasks starting on
gt;gt; gt;gt; gt; different
gt;gt; gt;gt; gt; months, a blank in the column would not be included in the COUNT and
gt;gt; gt;gt; gt; a
gt;gt; gt;gt; gt; zero
gt;gt; gt;gt; gt; would be included in the COUNT. ie. average = (sum of data) / COUNT
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; Hi!
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; If you only have 4 cells involved, what's wrong with:
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; =AVERAGE(B14,G14,L14,Q14)
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Biff
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; quot;jackquot; gt; wrote in message
gt;gt; gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;gt; gt;I use multiple columns per month and would like to do an average
gt;gt; gt;gt; gt;gt; gt;(and
gt;gt; gt;gt; gt;gt; gt;other
gt;gt; gt;gt; gt;gt; gt; functions) on past months to forcast a rate for future months.
gt;gt; gt;gt; gt;gt; gt; Since
gt;gt; gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt;gt; gt; columns that I want to average are not adjacent, I cannot find a
gt;gt; gt;gt; gt;gt; gt; way
gt;gt; gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt;gt; gt; do
gt;gt; gt;gt; gt;gt; gt; it. Is there a function to identify the columns similar to the
gt;gt; gt;gt; gt;gt; gt; sumif
gt;gt; gt;gt; gt;gt; gt; function, where I can identify the column based on a heading, and
gt;gt; gt;gt; gt;gt; gt; then
gt;gt; gt;gt; gt;gt; gt; average (or stddev, variance) them. Another approach would be to
gt;gt; gt;gt; gt;gt; gt; index
gt;gt; gt;gt; gt;gt; gt; into
gt;gt; gt;gt; gt;gt; gt; or offset or indirect into the table selecting every fifth column
gt;gt; gt;gt; gt;gt; gt; ie.
gt;gt; gt;gt; gt;gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
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
quot;Biffquot; wrote:
gt; gt;Do you understand why the
gt; gt;times (*) between the two logical statements cond1*cond2?
gt;
gt; I'm guessing that what you're asking is why the above works and why the use
gt; of AND didn't work?
gt;
gt; When you use AND every argument must evaluate to TRUE and if an argument is
gt; an array then every element of the array must also evaluate to TRUE.
gt;
gt; Multiplying the arrays together only requires that the individual elements
gt; compared to each other evaluate to TRUE.
gt;
gt; Biff
gt;
gt; quot;jackquot; gt; wrote in message
gt; ...
gt; gt; OK, thanks after I did the post I continued to work with it an I finally
gt; gt; got
gt; gt; the CTRL SHIFT ENTER. I did not understand how to use that. I solved it
gt; gt; with an ISNUMBER function. It took me awhile because I was trying to do
gt; gt; an
gt; gt; IF(AND(cond1,cond2),result) but it wouldn't work. Do you understand why
gt; gt; the
gt; gt; times (*) between the two logical statements cond1*cond2?
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; The formula is an array formula. If you don't enter it as an array it
gt; gt;gt; won't
gt; gt;gt; work properly.
gt; gt;gt;
gt; gt;gt; To enter an array formula:
gt; gt;gt;
gt; gt;gt; Type the formula in the cell then, instead of hitting the ENTER key like
gt; gt;gt; you
gt; gt;gt; normally would you MUST use a combination of keys. Hold down both the
gt; gt;gt; CTRL
gt; gt;gt; key and the SHIFT key then hit ENTER. If done properly Excel will enclose
gt; gt;gt; the formula in squiggly braces { }. You cannot just type these braces in,
gt; gt;gt; you MUST use the key combo. Also, if you edit an array formula it must be
gt; gt;gt; re-entered as an array using the key combo.
gt; gt;gt;
gt; gt;gt; OK, to include cells with zero and exclude blank cells:
gt; gt;gt;
gt; gt;gt; Array entered:
gt; gt;gt;
gt; gt;gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;quot; quot;),B14:Q14))
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;jackquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Biff, I thought you had it. It seems that when the mod=2 is true, it
gt; gt;gt; gt; includes all the cells in the average b14,c14,d14,...,q14. The average
gt; gt;gt; gt; does
gt; gt;gt; gt; not skip the cells where the mod=2 is false. Also the ideal would be
gt; gt;gt; gt; to
gt; gt;gt; gt; include the cells with 0 or a number and exclude the blank cells. I
gt; gt;gt; gt; could
gt; gt;gt; gt; proabably work that part out.
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Biffquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Try this:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Array entered using the key combo of CTRL,SHIFT,ENTER:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =AVERAGE(IF(MOD(COLUMN(B14:Q14),5)=2,B14:Q14))
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; To exclude cells that contain 0:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =AVERAGE(IF((MOD(COLUMN(B14:Q14),5)=2)*(B14:Q14lt;gt;0 ),B14:Q14))
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Biff
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;jackquot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt; Thanks Biff, after I hit post, I realized that I should have given
gt; gt;gt; gt;gt; gt; more
gt; gt;gt; gt;gt; gt; information. That was an example. I use most of the 256 columns
gt; gt;gt; gt;gt; gt; frequently.
gt; gt;gt; gt;gt; gt; It is monthly projection data. The Heading option actually works
gt; gt;gt; gt;gt; gt; better
gt; gt;gt; gt;gt; gt; because of the unfixed columns (months can be added at the end of
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; sheet
gt; gt;gt; gt;gt; gt; without having to change any parameters). An additional problem is
gt; gt;gt; gt;gt; gt; that
gt; gt;gt; gt;gt; gt; for
gt; gt;gt; gt;gt; gt; an average of variable columns due to some tasks starting on
gt; gt;gt; gt;gt; gt; different
gt; gt;gt; gt;gt; gt; months, a blank in the column would not be included in the COUNT and
gt; gt;gt; gt;gt; gt; a
gt; gt;gt; gt;gt; gt; zero
gt; gt;gt; gt;gt; gt; would be included in the COUNT. ie. average = (sum of data) / COUNT
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; quot;Biffquot; wrote:
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt;gt; Hi!
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; If you only have 4 cells involved, what's wrong with:
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; =AVERAGE(B14,G14,L14,Q14)
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; Biff
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt; quot;jackquot; gt; wrote in message
gt; gt;gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt;gt; gt;I use multiple columns per month and would like to do an average
gt; gt;gt; gt;gt; gt;gt; gt;(and
gt; gt;gt; gt;gt; gt;gt; gt;other
gt; gt;gt; gt;gt; gt;gt; gt; functions) on past months to forcast a rate for future months.
gt; gt;gt; gt;gt; gt;gt; gt; Since
gt; gt;gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt;gt; gt; columns that I want to average are not adjacent, I cannot find a
gt; gt;gt; gt;gt; gt;gt; gt; way
gt; gt;gt; gt;gt; gt;gt; gt; to
gt; gt;gt; gt;gt; gt;gt; gt; do
gt; gt;gt; gt;gt; gt;gt; gt; it. Is there a function to identify the columns similar to the
gt; gt;gt; gt;gt; gt;gt; gt; sumif
gt; gt;gt; gt;gt; gt;gt; gt; function, where I can identify the column based on a heading, and
gt; gt;gt; gt;gt; gt;gt; gt; then
gt; gt;gt; gt;gt; gt;gt; gt; average (or stddev, variance) them. Another approach would be to
gt; gt;gt; gt;gt; gt;gt; gt; index
gt; gt;gt; gt;gt; gt;gt; gt; into
gt; gt;gt; gt;gt; gt;gt; gt; or offset or indirect into the table selecting every fifth column
gt; gt;gt; gt;gt; gt;gt; gt; ie.
gt; gt;gt; gt;gt; gt;gt; gt; average(b14:q14,5) average the values at b14,g14,L14,q14
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
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
quot;jackquot; wrote:
gt; I use multiple columns per month and would like to do an average (and other
gt; functions) on past months to forcast a rate for future months. Since the
gt; columns that I want to average are not adjacent, I cannot find a way to do
gt; it. Is there a function to identify the columns similar to the sumif
gt; function, where I can identify the column based on a heading, and then
gt; average (or stddev, variance) them. Another approach would be to index into
gt; or offset or indirect into the table selecting every fifth column ie.
gt; average(b14:q14,5) average the values at b14,g14,L14,q14
留言列表
