I need to define a name for 31 cells in a worksheet. They are all in the
same column, but they are about every 11th line. Excel will only let me use
23 of those 31 cells in my range. I need this because it also, will not let
me average those cells, which is ultimately what I am trying to do. Any
advise would be appreciated. Thanks

Georgia

The problem is probably the length of the Refersto string when Excel appends
the sheetname.

Select all of the cells, using Ctrl-click, and then just type the name into
the Names box (the box to the left of the formula bar that shows the active
cell.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RadioTraffic" wrote in message
...
I need to define a name for 31 cells in a worksheet. They are all in the
same column, but they are about every 11th line. Excel will only let me
use
23 of those 31 cells in my range. I need this because it also, will not
let
me average those cells, which is ultimately what I am trying to do. Any
advise would be appreciated. Thanks

Georgia

Since you're interested in getting an average, you could try something like
this, without using a named range:

Say data starts in A2, and goes down to A332.

Values are in every 11th row, A2, A13, A24, ... A332

=SUMPRODUCT((MOD(ROW(A2:A332)-2,11)=0)*A2:A332)/SUMPRODUCT((MOD(ROW(A2:A332)-2,11)=0)*(A2:A332<0))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RadioTraffic" wrote in message
...
I tried doing this as you suggested. It still only wants to include 22
days.
Here is what I am doing. I work for a cluster of 4 radio station. I need
to average the number of spots missed per station each month. So I have
12
spreadsheets setup. Monthly information for each station goes into the
same
months sheet (ie January07) Each station is in it's own column. Only
the
first of the 4 stations listed is causing a problem. I have the same
scenerio in the other three and it doesn't have a problem doing this. Any
help is greatly appreciated. Thanks!
Georgia

"Bob Phillips" wrote:

The problem is probably the length of the Refersto string when Excel
appends
the sheetname.

Select all of the cells, using Ctrl-click, and then just type the name
into
the Names box (the box to the left of the formula bar that shows the
active
cell.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RadioTraffic" wrote in message
...
I need to define a name for 31 cells in a worksheet. They are all in
the
same column, but they are about every 11th line. Excel will only let
me
use
23 of those 31 cells in my range. I need this because it also, will
not
let
me average those cells, which is ultimately what I am trying to do.
Any
advise would be appreciated. Thanks

Georgia

As an after-thought ... you might have 0's in the data and you might *need*
to include them in the calculation, so you might wish to change the count
criteria from:
<0
To
<""

=SUMPRODUCT((MOD(ROW(A2:A332)-2,11)=0)*A2:A332)/SUMPRODUCT((MOD(ROW(A2:A332)-2,11)=0)*(A2:A332<""))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
Since you're interested in getting an average, you could try something
like this, without using a named range:

Say data starts in A2, and goes down to A332.

Values are in every 11th row, A2, A13, A24, ... A332

=SUMPRODUCT((MOD(ROW(A2:A332)-2,11)=0)*A2:A332)/SUMPRODUCT((MOD(ROW(A2:A332)-2,11)=0)*(A2:A332<0))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RadioTraffic" wrote in message
...
I tried doing this as you suggested. It still only wants to include 22
days.
Here is what I am doing. I work for a cluster of 4 radio station. I
need
to average the number of spots missed per station each month. So I have
12
spreadsheets setup. Monthly information for each station goes into the
same
months sheet (ie January07) Each station is in it's own column. Only
the
first of the 4 stations listed is causing a problem. I have the same
scenerio in the other three and it doesn't have a problem doing this.
Any
help is greatly appreciated. Thanks!
Georgia

"Bob Phillips" wrote:

The problem is probably the length of the Refersto string when Excel
appends
the sheetname.

Select all of the cells, using Ctrl-click, and then just type the name
into
the Names box (the box to the left of the formula bar that shows the
active
cell.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RadioTraffic" wrote in message
...
I need to define a name for 31 cells in a worksheet. They are all in
the
same column, but they are about every 11th line. Excel will only let
me
use
23 of those 31 cells in my range. I need this because it also, will
not
let
me average those cells, which is ultimately what I am trying to do.
Any
advise would be appreciated. Thanks

Georgia