Define range to avoid circular reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 30 Sep 2006 16:58 GMT

Is there any way to exclude a cell from a range.

For example, in Cell F10, I'd like to calculate the sum of all OTHER cells
in that row, excluding F10 itself. But SUM(A10:Z10) includes the cell F10,
and therefore generates a circular reference.

If I were doing a single cell, I could say SUM(A10:E10) + SUM(G10:Z10).
However, when I need a similar calculation for all cells in a range, this
would be a chore to create these formulas manually for each cell.

I think what I need is a range operator to define a range which excludes
single cells, something like "A10:Z10 excluding F10".

Any suggestions?

Thanks.

Reply to this Message

Don Guillett - 30 Sep 2006 17:23 GMT

=SUM(B8:C8,E8:F8)

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Is there any way to exclude a cell from a range.
>
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message

Bob Umlas - 30 Sep 2006 17:25 GMT

while in F10, define a name, say "AllButF" with a refersto as
=SUM($A10:$E10,$G10:$IV10)
then anywhere in a column you can enter =AllButF and you'll see what you
want. That is, if you enter =AllButF in cell F2, it will add A2:E2,G2:IV2.

> Is there any way to exclude a cell from a range.
>
[quoted text clipped - 13 lines]
>
> Thanks.

Reply to this Message

Eric - 30 Sep 2006 17:59 GMT

Bob -

This seems promising. But I think it breaks down at the edge of the range,
doesn't it? Using your example, it would work for a cell in colums B, C, D,
etc. But if I were to insert in A2, the range reference would try to refer
to the cell to the left of A2, which of course doesn't exist, so I'd
presumably get a NA value. What I really want in this case is a null range.
But probably I can resolve this by just including a one-column buffer zone
around the range I need to check.

Thanks for the suggestion.

Eric

> while in F10, define a name, say "AllButF" with a refersto as
> =SUM($A10:$E10,$G10:$IV10)
[quoted text clipped - 18 lines]
> >
> > Thanks.

Reply to this Message

Bruno - 30 Sep 2006 17:30 GMT

Hello Eric,
what you need is maybe SUMIF(range,criteria,sumrange) function. You
need to define a row (which you allready have) with criteria form
summing your numbers.
eg:range A11:Z11,criteria 0,sumrange A10:Z10 - SUMIF(A11:Z11;0;A10:Z10)
In cell F11 insert 1!
Bruno.

Reply to this Message