|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
=SUM(B8:C8,E8:F8)
--
Don Guillett
SalesAid Software
dguillett1[ at ]austin.rr.com
"Eric" <Eric[ at ]discussions.microsoft.com> wrote in message
news:80208E86-F91D-4D5E-B857-599C33B7A17E[ at ]microsoft.com...
[Quoted Text] > 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.
|
|
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.
"Eric" <Eric[ at ]discussions.microsoft.com> wrote in message
news:80208E86-F91D-4D5E-B857-599C33B7A17E[ at ]microsoft.com...
[Quoted Text] > 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.
|
|
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.
|
|
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
"Bob Umlas" wrote:
[Quoted Text] > 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.
>
> "Eric" <Eric[ at ]discussions.microsoft.com> wrote in message
> news:80208E86-F91D-4D5E-B857-599C33B7A17E[ at ]microsoft.com...
> > 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.
>
>
>
|
|
|