is there a vba function or excel formula to avoid circular reference error?

[outlawdevil]

I am trying to paydown loan balance $2 increment on loan balance of $14 if my checking account is greater than $2. I wanted to paid in full eventually so I keep reducing my loan balance if there is a payment. If I used both If & to validate condition statement in my formula, it runs in to cirular reference. Anyone knows if there is a better way to do this? Please see the attached image for example. Thanks.

 

[6StringJazzer]

A circular reference is not a formula error, it is a logic error. You need to rethink how you are solving your problem. What are the formulas in column E?

 

[outlawdevil]

A circular reference is not a formula error, it is a logic error. You need to rethink how you are solving your problem. What are the formulas in column E?

It’s equal to cell B2 and down,I think that’s why it’s creating the circular reference but I do need that in order to check If I have enough money to pay off the loan.

 

[joeu2004]

Please see the attached image for example. Thanks.

Contrary to the adage, "a picture is __not__ worth 1000 words", usually. At the very least, you should __tell__ us what all the formulas are. Even better, use the XL2BB add-in to copy-and-paste the range A1:E8 into your posting. See my example below.

We might __guess__ that the formulas in E2:E6 are of the form =B2, and the formula in E8 is =SUM(E2:E7). That would create the circular reference in B2.

BTW, the correct syntax for your formula in B2 is:

=IF(AND(C2>2, E8>0), -2, 0)

"&" is __not__ the "and" operator in Excel. And do not put double-quotes around numbers; "0" is text.

Be that as it may, although I am not enamored to your design overall, the following would correct both design problems.

Book1
	A	B	C	D	E
1		Amt	Ckg bal		Loan
2	init bal		7		14
3	pmt 1	-2	5		-2
4	pmt 2	-2	3		-2
5	pmt 3	-2	1		-2
6	pmt 4	0	1		0
7	pmt 5	0	1		0
8
9					8
Sheet1

Formulas:
B3: =IF(AND(C2>2, SUM($E$2:E2)>0), -2, 0)
C3: =C2+B3
E3: =B3
E9: =SUM(E2:E8)
Copy B3:E3 into B4:E7

(With XL2BB, we can see the formulas by selecting each cell.)

The key is to calculate the balance of the loan separately in column B, instead of relying on the balance that is calculated in E9.

 

[outlawdevil]

Contrary to the adage, "a picture is __not__ worth 1000 words", usually. At the very least, you should __tell__ us what all the formulas are. Even better, use the XL2BB add-in to copy-and-paste the range A1:E8 into your posting. See my example below.

We might __guess__ that the formulas in E2:E6 are of the form =B2, and the formula in E8 is =SUM(E2:E7). That would create the circular reference in B2.

BTW, the correct syntax for your formula in B2 is:

=IF(AND(C2>2, E8>0), -2, 0)

"&" is __not__ the "and" operator in Excel. And do not put double-quotes around numbers; "0" is text.

Be that as it may, although I am not enamored to your design overall, the following would correct both design problems.

Book1
	A	B	C	D	E
1		Amt	Ckg bal		Loan
2	init bal		7		14
3	pmt 1	-2	5		-2
4	pmt 2	-2	3		-2
5	pmt 3	-2	1		-2
6	pmt 4	0	1		0
7	pmt 5	0	1		0
8
9					8
Sheet1

Formulas:
B3: =IF(AND(C2>2, SUM($E$2:E2)>0), -2, 0)
C3: =C2+B3
E3: =B3
E9: =SUM(E2:E8)
Copy B3:E3 into B4:E7

(With XL2BB, we can see the formulas by selecting each cell.)

The key is to calculate the balance of the loan separately in column B, instead of relying on the balance that is calculated in E9.

Thank you so much, is there to add where it will figure out to pay the remaining balance if I replenish the funds later on. I am struggle with the remaining funds. Thanks.

 

[joeu2004]

is there to add where it will figure out to pay the remaining balance if I replenish the funds later on. I am struggle with the remaining funds.

I'm afraid I do not understand the question well enough to craft a solution.

In general, I would keep cumulative balances (checking account; loan) separate from changes (payments; deposits). And you might need separate columns of changes: one for the checking account; another for the loan. Use positive and negative values to distinguish deposits and payments (withdrawals).

You did that with the checking account (columns B and C). But for the loan, you mix balances (E2 and E9) and changes (E3:E7) in the same column.

 

[outlawdevil]

I'm afraid I do not understand the question well enough to craft a solution.

In general, I would keep cumulative balances (checking account; loan) separate from changes (payments; deposits). And you might need separate columns of changes: one for the checking account; another for the loan. Use positive and negative values to distinguish deposits and payments (withdrawals).

You did that with the checking account (columns B and C). But for the loan, you mix balances (E2 and E9) and changes (E3:E7) in the same column.

I am sorry I modified the formula a little bit to ensure if checking account allows I can paydown fast. My question will be, I want the formula to pick up that remaining $1 if possible , I know this will probably run into circular reference again. Thanks.

 

[joeu2004]

I am sorry I modified the formula a little bit to ensure if checking account allows I can paydown fast. My question will be, I want the formula to pick up that remaining $1 if possible

I won't be able to help you further. I do not understand the change that you made, and your intentions are not clear to me.

I hope someone else will step in to work with you. Good luck!

 

[outlawdevil]

I won't be able to help you further. I do not understand the change that you made, and your intentions are not clear to me.

I hope someone else will step in to work with you. Good luck!

Sorry, you have helped me a great deal. I changed the formula so I could pay the largest amount possible if my checking balance is greater than 2. My only problem is for this formula somehow to validate the last piece so it doesn’t go thru $14 loan balance. As you can see if I continue to pull down the formula, it will overpay.

 

[6StringJazzer]

It would help to show how your checking account increases in value after you take money out.