I want to average up to 10 columns of data,and display the information into 4
percentages. I would like to use the same spreadsheet to average less than
10 columns, as well. My question is how to set up the spreadsheet to average
the information based on the number of columns. I have it set up to average
everything as long as I have 10 sets of information, but sometimes I will
only use 7 or 8, etcc.....

I'm not quite sure I understand your question. Is there any kind of logic to
which cells should be averaged? Is it always start with the left most cell
of your range and include <x number of cells to the right? If so you can
use this the following. Assume A1 is the left-most cell of your range,
change the 8 to how many cells to include:

=AVERAGE(OFFSET(A1,,,,8))

--
Regards,
Dave


"Tim" wrote:

I want to average up to 10 columns of data,and display the information into 4
percentages. I would like to use the same spreadsheet to average less than
10 columns, as well. My question is how to set up the spreadsheet to average
the information based on the number of columns. I have it set up to average
everything as long as I have 10 sets of information, but sometimes I will
only use 7 or 8, etcc.....

David,
I have my spreadsheet to accept 6 values and store those values in a
separate column. The spreadsheet stores 10 sets of data in 10 separate
columns, and then it averages the data to total the percentages. The
percentages are broken down into 4 different calculations based on the 10
sets of numbers. It works well when I have 10 sets of data to use, but on
occasion, I will only have 6 or 8 sets of data to use. The formula I am
using for 1 of the calculations is =AVERAGE (B:K:). I don't know if I am
expressing myself correctly, but I want to have the K: to be variable so it
doesn't look for 10 sets of data every time. I appreciate any help you can
give me, but I can't remember how to make the K: value to be variable.....

"David Billigmeier" wrote:

I'm not quite sure I understand your question. Is there any kind of logic to
which cells should be averaged? Is it always start with the left most cell
of your range and include <x number of cells to the right? If so you can
use this the following. Assume A1 is the left-most cell of your range,
change the 8 to how many cells to include:

=AVERAGE(OFFSET(A1,,,,8))

--
Regards,
Dave


"Tim" wrote:

I want to average up to 10 columns of data,and display the information into 4
percentages. I would like to use the same spreadsheet to average less than
10 columns, as well. My question is how to set up the spreadsheet to average
the information based on the number of columns. I have it set up to average
everything as long as I have 10 sets of information, but sometimes I will
only use 7 or 8, etcc.....

Hmm, so you are trying to compute the average of entire columns, starting in
column B?

Why are you bothering to specify a range? It shouldn't be a speed issue
because the time difference between calculating AVERAGE(B:K) and AVERAGE(B:B)
is very small. The reason I ask is AVERAGE() automatically ignores cells
that are blank. So if you have the function AVERAGE(B:K), but column K is
completely empty, Excel will automatically ignore column K.

Maybe try this if you really want to ignore the columns that aren't
populated, modified a bit of course to match the first row of your data:
=AVERAGE(OFFSET(B1,,,65536,LOOKUP(2,1/(B1:K1<""),COLUMN(B1:K1))))
--
Regards,
Dave


"Tim" wrote:

David,
I have my spreadsheet to accept 6 values and store those values in a
separate column. The spreadsheet stores 10 sets of data in 10 separate
columns, and then it averages the data to total the percentages. The
percentages are broken down into 4 different calculations based on the 10
sets of numbers. It works well when I have 10 sets of data to use, but on
occasion, I will only have 6 or 8 sets of data to use. The formula I am
using for 1 of the calculations is =AVERAGE (B:K:). I don't know if I am
expressing myself correctly, but I want to have the K: to be variable so it
doesn't look for 10 sets of data every time. I appreciate any help you can
give me, but I can't remember how to make the K: value to be variable.....

"David Billigmeier" wrote:

I'm not quite sure I understand your question. Is there any kind of logic to
which cells should be averaged? Is it always start with the left most cell
of your range and include <x number of cells to the right? If so you can
use this the following. Assume A1 is the left-most cell of your range,
change the 8 to how many cells to include:

=AVERAGE(OFFSET(A1,,,,8))

--
Regards,
Dave


"Tim" wrote:

I want to average up to 10 columns of data,and display the information into 4
percentages. I would like to use the same spreadsheet to average less than
10 columns, as well. My question is how to set up the spreadsheet to average
the information based on the number of columns. I have it set up to average
everything as long as I have 10 sets of information, but sometimes I will
only use 7 or 8, etcc.....