Help with circular reference

[snuffnchess]

I cannot seem to add in an additional statement to this and was hoping somebody could help??

Please Login or Register  to view this content.

I cannot see the circular reference in this.... where is it at?? how can i fix it??

[Pete_UK]

What cell is the formula in?

Pete

[snuffnchess]

Sorry... i thought reply had posted.... it is in I20

If i put the formula in a different cell it works without the circular reference.

Here is what the cells look like

4-12-2016 8-32-00 AM.png

U20 looks like

Please Login or Register  to view this content.

J20 looks like

Please Login or Register  to view this content.

I20 is currently

Please Login or Register  to view this content.

Basically my goal is IF J20 is less than 90 AND U20 is 67.50, THEN J20=90

[Pete_UK]

So perhaps one of the cells in the formula (i.e. D20, E20, F20, G20 or U20) refer to I20, or to some other cell which themselves make use of I20. You need to examine those cells and any other dependent cells.

If it is still not obvious, then attach the offending workbook (remove any personal data beforehand).

Hope this helps.

Pete

[snuffnchess]

Edited post above with formulas and screen shots... may have come through after you posted reply??

[snuffnchess]

I cannot seem to get it to work where if the billable weight is under 90, AND U20 = 67.50, that billable weight =90. If u20=0, then billable weight should follow the same formula

Please Login or Register  to view this content.

I could have a hidden cell that has a 90 in it if u20=67.50, and then do a max function with that included?

[Pete_UK]

I've only just seen your edited Post #3. The formula for J20 refers to I20 in the MAX part. The formula for U20 refers to J20. The formula for I20 in your first post referred to U20, hence circular reference.

Hope this helps.

Pete

[snuffnchess]

Gotcha.

How would you recommend going about adding in to the "max" part of the J20 Formula if U20=67.50, then max, (I20, C20, 90) without the circular warning?

[Pete_UK]

It's difficult to put this in context without seeing your workbook - please attach it, as requested in Post #4. I am not able to view .png files on this forum due to software incompatibilities with some browsers.

Pete

[snuffnchess]

Here you go... sorry i missed that request

[Pete_UK]

I don't get a circular reference warning when I open that file.

Pete

[snuffnchess]

I had to remove it because it was breaking the file
What I am trying to accomplish essentially is in J20, Have it consider IF U20=67.50, AND I20<90, then I20 needs to be considered as 90.

[Pete_UK]

A formula in J20 can't change the value in I20 - I'm confused !!

There are other conditions with those two variables. What happens if U20=67.5 and I20 is greater than 90? What if U20 does not equal 67.5 ?

Pete

[snuffnchess]

the formula works perfectly except for for IF U20 = 67.50, AND I20<90 then I need the billable weight to be 90.
If u20 = 67.50, and I20>=90, then calculate the way it is now. If u20=0, then calculate the way it is now.

[Pete_UK]

Well, we're going round in circles here. The formula in U20 starts off along the lines of =IF(J20=something... and you want the formula in J20 to start off along the lines of =IF(U20=something...

You'll have to re-think your logic on this one.

Pete

[snuffnchess]

So I have been thinking about it overnight.... and I do get what you are saying. So then I supposed into J20, needs to have an additional if/then to encompass the formula that calculates U20, so that if whatever exists to make u20=67.50, then the "max" formula needs to consider 90 as an option in the weight comparisons....

In just logical steps, I am thinking then:

IF

(COUNTA(D20:F20)<>3,0,IF(SUM(MAX(D20:F20),(MEDIAN(D20:F20)*2),(MIN(D20:F20)*2))>130

Then

IF(ISBLANK(G20),IF(ISBLANK(C20),0,ROUNDUP(MAX(I20,C20,90),0)),"Letter")

Else

IF(ISBLANK(G20),IF(ISBLANK(C20),0,ROUNDUP(MAX(I20,C20),0)),"Letter")

Next question, then becomes is there a pretty way to neaten up the formula and/or a better way to program/calculate this so that it does not come across too messy???

[snuffnchess]

Okay.. I think i got it.... Now i just do not know if there is a way to pretty up the code.

J18 contains:

Please Login or Register  to view this content.

BE18 contains:

Please Login or Register  to view this content.

which is almost exactly the same as what is in U18.

It gets around the Circular reference.... Your thoughts????