Define range to avoid circular reference

[Guest]

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.

 

[Don Guillett]

=SUM(B8:C8,E8:F8)

 

[Bob Umlas]

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.

 

[Bruno]

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.

 

[Guest]

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