repeating columnar data

J

jack

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
 
B

Biff

Hi!

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

=AVERAGE(B14,G14,L14,Q14)

Biff
 
J

jack

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
 
B

Biff

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
 
J

jack

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.
 
B

Biff

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
 
J

jack

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?
 
B

Biff

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

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
 
J

jack

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
 
A

asas

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
 
Top