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.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][TABLE="width: 1131"]
<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>[TR]
[TD="class: xl66, width: 68, bgcolor: transparent"]Date[/TD]
[TD="class: xl65, width: 85, bgcolor: transparent"]QC# /Lims #[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]Control Lot[/TD]
[TD="class: xl65, width: 62, bgcolor: transparent"]Test Lot[/TD]
[TD="class: xl73, width: 100, bgcolor: transparent"]Ctrl Cell Density (10e5)[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]Ave. to date (10e5)[/TD]
[TD="class: xl72, width: 92, bgcolor: transparent"]Current Control/ Ave.[/TD]
[TD="class: xl73, width: 93, bgcolor: transparent"]Control IgG 1 (ug/mL)[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]Ave. to date (ug/mL)[/TD]
[TD="class: xl72, width: 92, bgcolor: transparent"]Current Control/ Ave.[/TD]
[TD="class: xl73, width: 83, bgcolor: transparent"]Control IgG 2 (ug/mL)[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]Ave. to date (ug/mL)[/TD]
[TD="class: xl72, width: 92, bgcolor: transparent"]Current Control/ Ave.[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Ctrl Cell Viability %[/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"]Ave. to date [/TD]
[TD="class: xl65, width: 109, bgcolor: transparent"]Current Control/ Ave.[/TD]
[TD="class: xl65, width: 131, bgcolor: transparent"]Cell Density Greater Than Average[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]16-Feb-99[/TD]
[TD="class: xl68, bgcolor: transparent"]8985[/TD]
[TD="class: xl68, bgcolor: transparent"]T13803[/TD]
[TD="class: xl68, bgcolor: transparent"]T16012[/TD]
[TD="class: xl70, bgcolor: transparent"]17.10[/TD]
[TD="class: xl70, bgcolor: transparent"]17.10[/TD]
[TD="class: xl74, bgcolor: transparent"]100.0%[/TD]
[TD="class: xl70, bgcolor: transparent"]75.40[/TD]
[TD="class: xl70, bgcolor: transparent"]N/A[/TD]
[TD="class: xl71, bgcolor: transparent"]N/A[/TD]
[TD="class: xl70, bgcolor: transparent"]103.90[/TD]
[TD="class: xl70, bgcolor: transparent"]N/A[/TD]
[TD="class: xl71, bgcolor: transparent"]N/A[/TD]
[TD="class: xl69, bgcolor: #a5a5a5"] [/TD]
[TD="class: xl69, bgcolor: #a5a5a5"] [/TD]
[TD="class: xl69, bgcolor: #a5a5a5"] [/TD]
[TD="class: xl68, bgcolor: transparent"]yes[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]8-Jun-99[/TD]
[TD="class: xl68, bgcolor: transparent"]9364[/TD]
[TD="class: xl68, bgcolor: transparent"]T16012[/TD]
[TD="class: xl68, bgcolor: transparent"]U16702[/TD]
[TD="class: xl70, bgcolor: transparent"]12.80[/TD]
[TD="class: xl70, bgcolor: transparent"]14.95[/TD]
[TD="class: xl74, bgcolor: transparent"]85.6%[/TD]
[TD="class: xl70, bgcolor: transparent"]74.37[/TD]
[TD="class: xl70, bgcolor: transparent"]74.89[/TD]
[TD="class: xl71, bgcolor: transparent"]99%[/TD]
[TD="class: xl70, bgcolor: transparent"]151.98[/TD]
[TD="class: xl70, bgcolor: transparent"]127.94[/TD]
[TD="class: xl71, bgcolor: transparent"]119%[/TD]
[TD="class: xl69, bgcolor: #a5a5a5"] [/TD]
[TD="class: xl69, bgcolor: #a5a5a5"] [/TD]
[TD="class: xl69, bgcolor: #a5a5a5"] [/TD]
[TD="class: xl68, bgcolor: transparent"]yes[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Asala42]

In 2010 go to File/Options/Formulas --upper right side is a checkbox for "Enable iterative calculation"
In early versions of excel its under Tools/Options/Calculation

Only do this if this was intended as an iterative calculation. This does not solve the problem other than covering it up if there really is just a formula error somewhere.

 

[shawnhet]

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.

Maybe it's just me but I can't figure out what your problem is at its root. Your AverageIF looks fine to me to set up a running AverageIf and when I post that formula into a new sheet I don't get a CR. Can you post your AverageIf as it appears in your expression (ie including the Sheet references) and tell me cell(s) the AverageIf is in (you state that it is on another sheet).

Cheers,

 

[smcghee]

Maybe it's just me but I can't figure out what your problem is at its root. Your AverageIF looks fine to me to set up a running AverageIf and when I post that formula into a new sheet I don't get a CR. Can you post your AverageIf as it appears in your expression (ie including the Sheet references) and tell me cell(s) the AverageIf is in (you state that it is on another sheet).

Cheers,

shawnhet,

Try to place the AIF within cell F, if I am not mistaken. The running AIF that I have seperate from the data works perfectly but I need that number to also display within the F cell. So when I place an AverageIf within the F cell, I receive a CR.

So again just in case. In cell E data is entered from the user, Cell F calculates the Average based off of the entry and the previous running Average. The problem I have is that the Average should be an AIF because if the data entered is below 70% (based on Cell G which is implied from the entry Cell E and the running Average in Cell F) it would exclude it from the calculation. The running Average can not do this but the seperate AIF that I have applied does. So this means I would need to include a running AIF but when I do I receive a CR from the data.

 

[shawnhet]

smghee,

I still don't follow what you are saying exactly, can you try and post the expression you are *trying* to execute that gives you the CR? Trying to guess exactly what is causing you the problem is pretty difficult.

Cheers,