[Solved] Assistance to eliminate a circular reference

[KlingonTrucker]

Hi,

I may have difficulty explaining my dilemma. I have a sheet with a calculated field that I want to read $0.00 if the addition of the calculation to a total, causes it to be greater than another calculated field.

So far, my attempts to work this out causes it to refer to itself causing a circular reference. I'm not opposed to doing a bunch of calculations in different cells to get where I want but I can't figure out how to stop it referring back to itself as part of the comparison.

Attached is the sheet. I've marked the troublesome cell (P4) in purple. Essentially it should read 0.00 with the data I've put in because C23 is a negative figure. And I can do THAT with a nested if(). the problem comes from the fact that P4 itself can cause c23 to become negative and that's where I get lost. how do I make P4 read $0.00 if it makes C23 go negative when it has its calculated value?

I admit some of my work on this sheet is kind of sloppy and brute force ish. and I'm okay with inelegant solutions so long as they work.

thanks in advance.

[Alex1]

Welcome to the forum!
P4 should somehow depend upon C21-G19-M19-P3-SUM(P5:P18) instead of C23.

[Hagar Delest]

P4 should somehow depend upon C21-G19-M19-P3-SUM(P5:P18) instead of C23.

It is a tricky situation since P4 is also a cost that should be taken into account. However it has to be so only in case of a [positive] benefit.

I think that you need to place the Efund as the first cost of the column so that the total is the sum of the range starting under Efund, then add a condition for EFund (now in P3) to set it to 0 if the benefit is negative. And have another couple of cells to calculate the costs with the Efund and the actual benefit taking into account those full costs.
You can add an IF in the second benefit calculation so that it remains blank if the first one is negative. Else, it will show the same benefit when positive.

[Alex1]

Try this in P4: =IF(C21-G19-M19-P3-SUM(P5:P18)+IF(S22="Fixed";S24;IF(S22="Percentage";C19*S24;B19*S24))<0;0;IF(S22="Fixed";S24;IF(S22="Percentage";C19*S24;B19*S24)))

[KlingonTrucker]

Welcome to the forum!
P4 should somehow depend upon C21-G19-M19-P3-SUM(P5:P18) instead of C23.

Thank you. You are correct. that's most of what I was missing... compare to the parts, not the sum total.
Good spotting.

[KlingonTrucker]

Try this in P4: =IF(C21-G19-M19-P3-SUM(P5:P18)+IF(S22="Fixed";S24;IF(S22="Percentage";C19*S24;B19*S24))<0;0;IF(S22="Fixed";S24;IF(S22="Percentage";C19*S24;B19*S24)))

I kind of did this after your first suggestion but I "outsourced" it, did the math in a different spot on the sheet and just plugged the final result into P4.

Thanks again.

[Cazer]

Hey there @KlingonTrucker,
I think you're on the right track with your nested IFs, and @Alex1's suggestion is on point. P4 should depend on C21-G19-M19-P3-SUM(P5:P18) instead of C23.
To avoid making C23 go negative, you could calculate P4 as the lesser of your current calculation or (C21-G19-M19-P3-SUM(P5:P18)). This way, you're ensuring that your addition won't exceed the total you're comparing it against.
It might look something like this: =MIN(your_current_calculation, C21-G19-M19-P3-SUM(P5:P18)). This should prevent C23 from going negative due to P4's calculation.
Keep going, you're almost there! Don't worry about it being inelegant. If it works, it works!