Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2006

Tip: Looking for answers? Try searching our database.

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

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.