Lori

Info only...............

Note: there is a limit of about 25 - 30 cells to a range using this method, due
to a 255 character limit in a named range, and to the fact that the cells in
'Refers to' field get the sheet name pre-pended to them. Longer sheet names will
reduce the this number even more.

This can be circumvented, if more are needed, they can be manually entered in
the 'Refers to' box.

Whilst the limit is 255 characters in the 'Name' definition, as an example, it
is possible to define a range of 46 non-contiguous cells, with the following
string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4 ,$B$6,$D$6,$F$6,$H$6,$J$6,$B$8,$D$8,$F$8,
$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$1 2,$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,
$J$14,$B$16,$D$16,$F$16,$H$16,$J$16,$B$18,$D$18,$F $18,$H$18,$J$18,$L$3

As an extra hint. In the example above all of the cell references are absolute.
Typing all of that out can be time-consuming and difficult. It would be easier
to enter the cell references as relative references (=B2,D2,F2, etc.), and then
convert them to absolute simply by hitting F2 to enter edit mode, select the
complete string, and then F4 to convert to absolute. Further F4s will convert to
relative/absolute, absoulte/relative, and then back to relative.


Gord Dibben MS Excel MVP

On 30 Oct 2006 08:16:01 -0800, "Lori" wrote:

Roger, this only works for small ranges. Named ranges have a limit of
255 characters and references are stored in the format
=Sheet1!A1,Sheet1!A2,... which limits the selection to about 20 such
cells.

Roger Govier wrote:

Hi Tom

Create a named range for your cells to be averaged.
Select your range of cells by holding down Ctrl as you click on each one
Enter Myrange into the Name box, (just above row Number 1 and to the
left of column A) and press Enter.

=AVERAGE(Myrange)
--
Regards

Roger Govier


"Tom Young" <Tom wrote in message
...
I'm trying to average the values in approximately 250 non-adjacent
cells
(there is no pattern to the cell location).

My first obstacle was the 30 argument limit, which I hoped to get
around by
using =AVERAGE (()). In another thread someone referred to this as
using
"multiple area ranges."

That seemed to be working fine until about one hour into the tedious
process
of selecting cells while holding down ctrl, I get the "too many
characters"
notification.

Surely there is some efficient way of averaging more than 30
non-adjacent
cells?? I can't believe that Excel makes it this difficult to do a
simple
calculation. Any suggestions? Thanks.