Solved Why does the Circular Reference warning keep appearing

September 25, 2014 at 16:56:16
Specs: Windows 7
The function below is designed to give me the total of the last 2 values in a row. Whilst it appears to be working ok, whenever I reopen the file, a Circular Reference warning appears. Can you please advise me where I'm going wrong.

D2: = SUM(INDEX(2:2,LARGE(INDEX((H2:BA2<>"")*COLUMN(H2:BA2),0),2)):BA2)

Regards Chris


See More: Why does the Circular Reference warning keep appearing

Report •

✔ Best Answer
September 26, 2014 at 12:36:50
Try this:

=IF(COUNT(H2:BA2)=0,"",SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2))

That should take care of the #N/A error.

Is it possible to send you a copy of my worksheet somehow.
Sorry, there is no mechanism to upload a sheet on these forums,
but then that would defeat the purpose of the forums.
Others can't learn if they can't see what where doing.

Just remember when you post, that we can not see your spread sheet
nor do we know what your try to do, so you need to be detailed and
specific when post.

You can use the PRE tags to post an example of you sheets
like I did in your other post about Ranking numbers.
Read this HOW-TO for directions:

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
September 25, 2014 at 17:51:25
I know zilch about your problem.

See if this helps.

Circular Reference warning
http://is.gd/8y3Yyq


Report •

#2
September 25, 2014 at 18:12:11
Your INDEX is using ALL of row 2 which includes cell D2, which contains your formula, so you get Circular Reference.

It is rarely a good idea to use entire rows or columns, like 2:2 or A:A,
it best to use only the range you need.

And Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
September 25, 2014 at 19:51:24
Thanks Mike,
As suggested I altered the Index Range to H2:BA2, however it will now only record the first value entered and as soon as I enter a second value it records as zero

Report •

Related Solutions

#4
September 26, 2014 at 08:47:35
Without knowing what your sheets looks like, or what data is where,
the best I can offer is try this in D2, no guarantees:

=SUM(OFFSET(H2,0,COUNT(H2:BA2)-2,1,2))

MIKE

http://www.skeptic.com/


Report •

#5
September 26, 2014 at 10:25:53
Thanks Mike,
It works fine for my purposes and certainly alleviates the problem of the circular reference. A couple of issues that were evident however, firstly, the value placed in the first cell doubles its value when it appears in the function cell, however, when a value is placed in the second cell it corrects itself. Secondly, the function will only work providing there are values in all the cells to the left. If, for instance I put values in the last 2 cells only, it will not return a result.
Thanks again Mike

Report •

#6
September 26, 2014 at 10:37:52
Secondly, the function will only work providing there are values in all the cells to the left.

Like I said, without knowing how your data is set up, it's difficult to suggest a solution.

If some of the cells in your range are going to be blank, then try this, it's similar
to what you tried.

=SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2)

MIKE

http://www.skeptic.com/


Report •

#7
September 26, 2014 at 10:51:10
What do you mean by this:

firstly, the value placed in the first cell doubles its value when it appears in the function cell, however, when a value is placed in the second cell it corrects itself.

When a value is placed in the first cell, H2,
the formula, in cell D2, returns the value in H2,
it does not double it on my sheet.

When a second value is place in I2,
the formula returns the sum of H2 & I2

So I don't understand what your doing.

MIKE

http://www.skeptic.com/


Report •

#8
September 26, 2014 at 12:20:57
=SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2)

That works well except before I enter any data, "#N/A" appears in the function cell ?
Is it possible to send you a copy of my worksheet somehow.
Regarding the value doubling in the function cell, I cant explain why because when I performed the function on another worksheet, it worked perfectly
Regards
Chris


Report •

#9
September 26, 2014 at 12:36:50
✔ Best Answer
Try this:

=IF(COUNT(H2:BA2)=0,"",SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2))

That should take care of the #N/A error.

Is it possible to send you a copy of my worksheet somehow.
Sorry, there is no mechanism to upload a sheet on these forums,
but then that would defeat the purpose of the forums.
Others can't learn if they can't see what where doing.

Just remember when you post, that we can not see your spread sheet
nor do we know what your try to do, so you need to be detailed and
specific when post.

You can use the PRE tags to post an example of you sheets
like I did in your other post about Ranking numbers.
Read this HOW-TO for directions:

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#10
September 26, 2014 at 13:21:31
Cheers Mike,
All good now. Thanks for all your help
Regards Chris

Report •

#11
September 26, 2014 at 13:25:11
Here are a couple of updated formula, take your pick.

=IF(COUNT(H2:BA2)<=1,"",SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2))

or

=IF(ISERROR(SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2)),"",SUM(INDEX(H2:BA2,MATCH(257,H2:INDEX(H2:BA2,MATCH(257,H2:BA2)-1))):BA2))

In the older form, if you entered a number along the row, but not in H2 you could still get an error message..

MIKE

http://www.skeptic.com/


Report •

Ask Question