Formula Too Long

[Guest]

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.

 

[Guest]

=SUM([cells])/COUNT([cells]

But even there you may run into limits.

Alternatively, why don't you sum up a few groups of the cells, say 5 groups
of 50, sum those 5 sums together and divide by 250?

Dave

 

[Lori]

You could enter the cell references in a list on another sheet. Then
enter an array formula like (ctrl+shift+enter to execute):

=AVERAGE(N(INDIRECT(Sheet2!A1:A250)))

An alternative way to get the list of references would be to define the
name Selection to refer to =SELECTION() and then enter down the column:

=CELL("address",INDEX(Selection,,,ROW()))

Now when you select a range of cells and press Ctl+Alt+F9, the range
will update with the current selection, ignore any circular reference
messages. When you have selected all 250 references, copy and paste
values to fix the list.

 

[vezerid]

Tom, I did the following experiment:

In range A1:G22 I selected randomly 4 cells. I entered their addresses
in H1:H4 (e.g. A3, G4 etc).
Then I used the following formula:

=SUMPRODUCT(A1:G22*ISNUMBER(MATCH(ADDRESS(ROW(A1:G22),COLUMN(A1:G22),4),H1:H4,0)))/SUMPRODUCT(--ISNUMBER(MATCH(ADDRESS(ROW(A1:G22),COLUMN(A1:G22),4),H1:H4,0)))

It worked. Does this provide a basis for what you want to do?

One thing that can complicate the formula is having the cells in
different worksheets. ALso if the containing range is too big
SUMPRODUCT might have a problem. In this case you can use SUM instead
of SUMPRODUCT but you must array enter it (Ctrl+Shift+Enter).

Another thing that can simplify your task is to first color the cells
that you want to include and then use a user-defined formula to build
the list of cells automatically. There are several posts in this group
that show how to write such a function that can detect the format.

HTH
Kostis Vezerides

 

[Roger Govier]

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)

 

[Lori]

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.

 

[Gord Dibben]

Note: there is 255 character limit in the "refers to:" dialog so may not work.

Another note: hit SHIFT + F8 to enable "ADD" mode and you do not have to hold
CTRL key while selecting non-adjacent cells.


Gord Dibben MS Excel MVP

 

[Roger Govier]

Lori, you are quite right.

Tom could create a series of such ranges (within the limits for each)
and use rng1, rng2 etc as the names then
=AVERAGE(rng1,rng2, ..... rng13)
A bit messy, but it would work.

 

[Roger Govier]

Hi Gord

Note: there is 255 character limit

Noted!!! See my response to Lori

SHIFT + F8 to enable "ADD" mode

had not realised that, Gord, it makes it much simpler. Many thanks.

 

[Gord Dibben]

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$12,$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

 

[Lori]

Gord - This all works but entering text by hand into the name box is
not really practical and also error prone.

Listing the references on a sheet and using the indirect function
approach is not a bad solution as its quite clear and only needs
references in A1 format. The list of references can be obtained from
the current selection either with the method above (which worked well
in tests) or a more general VBA routine.

Roger's multiple range approach is also workable and simple but maybe a
little less transparent in that the ranges are all hidden in separate
names.

 

[Guest]

Thanks for the insight guys and multiple suggestions. Now i've got some
thinking to do in order to determine the best way to proceed.

On another front, I need to average several cells (not enough to trigger the
limits discussed above) that currently have nothing in them. I want to go
ahead and set the formula up though. By doing this, I get the #DIV/0! error.
Is there any way to have the result cell just be blank rather than #DIV/0!?
Of course, once some values are entered into the cells it will be a mute
point. But, that could be some time from now and I need to have the formulas
in place.

 

[Gord Dibben]

Good points Lori.

Thanks, Gord

Gord - This all works but entering text by hand into the name box is
not really practical and also error prone.

Listing the references on a sheet and using the indirect function
approach is not a bad solution as its quite clear and only needs
references in A1 format. The list of references can be obtained from
the current selection either with the method above (which worked well
in tests) or a more general VBA routine.

Roger's multiple range approach is also workable and simple but maybe a
little less transparent in that the ranges are all hidden in separate
names.

Gord Dibben MS Excel MVP

 

[Roger Govier]

Hi Tom

Something like
=IF(COUNT(G1:K1)=0,"",AVERAGE(G1:K1))

substituting whatever you have for your ranges in place of G1:K1

 

[Herbert Seidenberg]

In case you have a mixture of contiguous and non-contiguous cells
and you don't want to create a list of 250 addresses,
here is a variation on Lori's theme:
Select the 250 cells with Gord's method and conditionally format them
with any criteria. No other cells on that sheet should have CF.
Insert > Name > Define > My250
Refers To: =Selection()
Enter this formula into some cell:
=Average(My250)
Go To > Special > Conditional Formats
Ctrl+Alt+F9
Copy > Paste Special > Value

Lori: What are some other undocumented functions like Select and
Evaluate
that apply to Refers To?

 

[Lori]

Tom - To discount blanks in the above formula you could use:
IF(ISBLANK(...),"",N(...)) in place of N(...).

Herbert - Yes, this would work well for non-contiguous regions and
discount blanks. Commands for saving the current selection are View >
Custom Views > Add and File > Save Workspace which would allow the
selection to be easily recovered.

For other functions refer to the information returning functions in the
XL4 macro help file:

http://www.microsoft.com/downloads/...F7-D30E-4CE9-8930-5D03748CA5CD&displaylang=en

Another useful macro function in this context is REFTEXT but also has a
255 limit for others see the help file at: