Budget IF question

[brianpaulyo]

Okay guys. Here is a crazy one, but I am building some amazing personal budgeting sheets.

First, an explanation of what I am trying to do.

I have a row of numbers in 3 cells.

20 -20 0

20 is what I budgeted to pay of a debt.

-20 is what I owe on the debt. (retrieved from a cell in another sheet where I have lists of transactions like a checkbook register. Much like a quicken program)

0 is what is left over after paying the debt.

Now here is the tricky part.

When I clear the transaction, the cell in which I was retrieving the debt total now reads 0 because I paid the debt in full.

Now the cells look like this:

20 0 20

I want excel to show the last cell number as 0 because the debt is paid. Since I have my next months budget rolling over leftover amounts this is showing I have 20 to carry over to next months budget when really I don't because the 20 was spent to pay off the debt. Does this make sense?

I tried this If formula, but it doesn't do it right.

=E103+F103+IF((E103+F103)=E103,0,SUM(E103+F103))

Any ides?

 

[RCBricker]

Okay guys. Here is a crazy one, but I am building some amazing personal budgeting sheets.

First, an explanation of what I am trying to do.

I have a row of numbers in 3 cells.

20 -20 0

20 is what I budgeted to pay of a debt.

-20 is what I owe on the debt. (retrieved from a cell in another sheet where I have lists of transactions like a checkbook register. Much like a quicken program)

0 is what is left over after paying the debt.

Now here is the tricky part.

When I clear the transaction, the cell in which I was retrieving the debt total now reads 0 because I paid the debt in full.

Now the cells look like this:

20 0 20

I want excel to show the last cell number as 0 because the debt is paid. Since I have my next months budget rolling over leftover amounts this is showing I have 20 to carry over to next months budget when really I don't because the 20 was spent to pay off the debt. Does this make sense?

I tried this If formula, but it doesn't do it right.

=E103+F103+IF((E103+F103)=E103,0,SUM(E103+F103))

Any ides?

this is easier if you set up your workbook to work like a program like quicken.

I would create a worksheet for each debt which would assign a debt ID number. I would then use that sheet to keep a running balance on the debt. So your budget would look a the total debt owed rather than trying to maintain a running total from your registry. The registry is your dynamic activity which will feed back to your debt worksheets and those are dynamic and feed your budget worksheet.

thanks,

 

[brianpaulyo]

Not sure how that would solve the budget. Here is a link to download the workbook. Look at the budget tab where it shows Debt. This might clarify.

https://usana.box.com/s/ow3adikswppo2mdkeud8kapkcpfj20g4

Thanks for your help.

 

[RCBricker]

Not sure how that would solve the budget. Here is a link to download the workbook. Look at the budget tab where it shows Debt. This might clarify.

https://usana.box.com/s/ow3adikswppo2mdkeud8kapkcpfj20g4

Thanks for your help.

unfortunately I can not look at workbooks. I help on these forums while at work to give me a break from my own coding and analysis. The firewall will not allow me to view attachments and I have yet to see a situation that allows me to see even pics. Most of those sites are blocked.

It is frustrating as hell.

as for your comment about how it will help the budget... you are running your budget as a balance tracker rather than a budget. You should be tracking your balances as payments are made to the account, you should be planning your payments based on the current balance at the beginning of a given month. two separate tasks.


thanks

 

[wornhall]

=if(F103=0,0,E103+F103)

If the middle cell is zero, then the result is zero, otherwise the result is the sum of the two.