Circular Reference situation

[smcghee]

All,

I need severe help with completing this issue. I am trying to do a weird calculation that should work but I keep getting a circular referece error and I do not know why.

For example I have several columns from a - p and they contain data regarding testing. the first 5 columns require entry from the user, the next colum takes the previous column and calculates the average based on the previous row(s) and the current row. The next column does the percent of the average versus the input. I also added in an extra column (q) to see if the percentage is over 70% (this was done with an if statement).

So what I would like to do is create a running average if in another column but I keep getting a circular reference number. I have an overall sum that calculates the average based on whether or not it is over 70% and is on going but I need to apply that same rule to the entered values on that row. I keep getting a circular reference each time I try and apply the calculation. My guess is that I do not have predetermined values and it causes the error.

I was wondering if someone has a way around this. I included an example of the data. Let me know if you need further explanation.

Date QC# /Lims # Control Lot Test Lot Ctrl Cell Density (10e5) Ave. to date (10e5) Current Control/ Ave. Control IgG 1 (ug/mL) Ave. to date (ug/mL) Current Control/ Ave. Control IgG 2 (ug/mL) Ave. to date (ug/mL) Current Control/ Ave. Ctrl Cell Viability % Ave. to date Current Control/ Ave. Cell Density Greater Than Average 16-Feb-99 8985 T13803 T16012 17.10 17.10 100.0% 75.40 N/A N/A 103.90 N/A N/A yes 8-Jun-99 9364 T16012 U16702 12.80 14.95 85.6% 74.37 74.89 99% 151.98 127.94 119% yes <COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[Austrada]

Maybe you could post the example again but show your formulas? You can do this by pressing Ctrl + ` (The button on the left hand side of the 1) . Also include the formula you are trying to create that gives youa circular reference.

Thanks

 

[smcghee]

All,

I need severe help with completing this issue. I am trying to do a weird calculation that should work but I keep getting a circular referece error and I do not know why.

For example I have several columns from a - p and they contain data regarding testing. the first 5 columns require entry from the user, the next colum takes the previous column and calculates the average based on the previous row(s) and the current row. The next column does the percent of the average versus the input. I also added in an extra column (q) to see if the percentage is over 70% (this was done with an if statement).

So what I would like to do is create a running average if in another column but I keep getting a circular reference number. I have an overall sum that calculates the average based on whether or not it is over 70% and is on going but I need to apply that same rule to the entered values on that row. I keep getting a circular reference each time I try and apply the calculation. My guess is that I do not have predetermined values and it causes the error.

I was wondering if someone has a way around this. I included an example of the data. Let me know if you need further explanation.

Date QC# /Lims # Control Lot Test Lot Ctrl Cell Density (10e5) Ave. to date (10e5) Current Control/ Ave. Control IgG 1 (ug/mL) Ave. to date (ug/mL) Current Control/ Ave. Control IgG 2 (ug/mL) Ave. to date (ug/mL) Current Control/ Ave. Ctrl Cell Viability % Ave. to date Current Control/ Ave. Cell Density Greater Than Average 16-Feb-99 8985 T13803 T16012 17.10 17.10 100.0% 75.40 N/A N/A 103.90 N/A N/A yes 8-Jun-99 9364 T16012 U16702 12.80 14.95 85.6% 74.37 74.89 99% 151.98 127.94 119% yes <TBODY> </TBODY>

<TBODY>
</TBODY>

Sorry I am late in following up on my on question, just been busy and this is the first time I actually had all week to work on this issue. I have several formulas applied to environment. In column F I have the formula =IF(ISBLANK(E17),"",AVERAGE($E$2:E17)), in column g I have =IF(ISBLANK(E17), "",E17/F17), in column q I have =IF(ISBLANK(E17), "", IF(G17>=70%, "yes","no")). On another sheet I have =AVERAGEIF($Q$2:Q20,"yes",$E$2:E20) and =AVERAGE(G2:G20).

So what I noticed is that the average and the average if was not matching. Mainly because the average if takes out any value that is a No. I then concluded that I need to imply the Average if to the F column but each time I do I receive a circular reference error. I expect I am getting the error because it is trying to take the value of the current cell entry in E and the rest of the cells above from column E. I hope this helps.

 

[smcghee]

Can any one help with this?

 

[shawnhet]

Just quickly looking at your expressions, I notice that =AVERAGE(G2:G20) does not have any absolute references (no $ signs). When you copy this to another cell, you will change the range the address is taken over (which will change what the average is calculated as)

For instance, copying that expression down one cell will give =AVERAGE(G3:G21)

Anyways, IMO you should not try and use circular references. What exactly is it that is giving the wrong answers?

Cheers,

 

[smcghee]

Just quickly looking at your expressions, I notice that =AVERAGE(G2:G20) does not have any absolute references (no $ signs). When you copy this to another cell, you will change the range the address is taken over (which will change what the average is calculated as)

For instance, copying that expression down one cell will give =AVERAGE(G3:G21)

Anyways, IMO you should not try and use circular references. What exactly is it that is giving the wrong answers?

Cheers,

shawhet,

I understand that concept. I don't want to use circular reference. I keep getting it when I try to apply a calculation in range of the scope applied; as I stated above. I was hoping somone would give me another option to solve this problem. The issue is very simple to resolve, but the circular reference is the problem.

 

[shawnhet]

I don't see where the circular reference is coming in at all. None of the expressions you have posted would give you a circular reference, it seems to me. Since you are not getting the answers you are looking for, what exactly do you want? I am pretty confident that you can get the answers you are looking for without a circular reference but obviously the Average and AverageIf functions are not getting you there.

Cheers,

 

[Asala42]

Circular references are usually screw ups. There are situations where intentional circular references can be useful (iterative calculations), but from your description this does not seem to be the case.

Write your formulas as planned, and when you get the circular reference warning start tracing dependent formulas and look for errors. Look for any references to the formula cell that triggered the error. Often these can come up from simple typos, like summing a range that includes an unintended formula cell. It's also possible the design flow is flawed and you've accidentally introduced a calculation loop.

If I've misinterpreted the problem, such that the circular reference was intentional - say with an iterative calculation going on, you can bypass the error itself by checking "Enable Iterations" under calculation options. Do not do this if the circular reference was not planned. An unplanned circular reference needs to be resolved before you can trust any formula results displayed on the spreadsheet.

 

[smcghee]

I don't see where the circular reference is coming in at all. None of the expressions you have posted would give you a circular reference, it seems to me. Since you are not getting the answers you are looking for, what exactly do you want? I am pretty confident that you can get the answers you are looking for without a circular reference but obviously the Average and AverageIf functions are not getting you there.

Cheers,

I know...I said the same thing several times while creating the spreadsheet. The CR is applied because of how the AIF is being applied. Because the AIF is included in the range of values the CR is implied. For example for one line out of the spread sheet; If I tell the calculated values to get something that has yet been defined or is based on a value from that line, I get a CR. Not certain why because the values are defined based on the entry and for some reason a CR is applied each time. Maybe its because I am using Excel 2007.

 

[smcghee]

Circular references are usually screw ups. There are situations where intentional circular references can be useful (iterative calculations), but from your description this does not seem to be the case.

Write your formulas as planned, and when you get the circular reference warning start tracing dependent formulas and look for errors. Look for any references to the formula cell that triggered the error. Often these can come up from simple typos, like summing a range that includes an unintended formula cell. It's also possible the design flow is flawed and you've accidentally introduced a calculation loop.

If I've misinterpreted the problem, such that the circular reference was intentional - say with an iterative calculation going on, you can bypass the error itself by checking "Enable Iterations" under calculation options. Do not do this if the circular reference was not planned. An unplanned circular reference needs to be resolved before you can trust any formula results displayed on the spreadsheet.

where can I find that option; that maybe the key to my issue with this spread sheet.