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 - 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.
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.
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.
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.