Can you help me with a formula

[biggynbp]

I am trying to create a budget spreadsheet and running into some trouble with this concept. I have my budget set, I also have a column where i enter my payments. The payements are subtracted from the budget number which in turn gives me an "amount due." I also have another cell where i type "paid" and that zero's out the amount due cell. I am having trouble connecting the two formulas; calculating the amount due and at the same time entering 0 if it is paid in full

any suggestions to get Column D to populate a running balance and the ability to zero out if i enter paid in Column E?

 

[Joe4]

I am not sure your question is clear. Doesn't the formula you posted do what you want?
Maybe it would be better to walk us through an actual example, step-by-step, based on the sample data you posted, explaining exactly what SHOULD happen (be returned) and why.

By the way, in your formula, "0" will return the TEXT value 0, not the numeric value. If these values are being used in other calculations, it could cause errors, as some mathematical functions will not work on text values. Double-quotes around characters indicate text entries. Remove them from your formula, i.e.

=IF(E11="paid",0,B11)

 

[biggynbp]

Thank you for your reply.....If i don't use the quotes then the field is filled with a "-". What I am hoping to do, in addition to using that formula that puts a 0 in the amount due column is: when i enter a value in the actual column it will subtract it from the expected column and give the balance remaining in the amount due column. taking a look at the pics i posted you will see in the "rent row" when i entered paid in the "status" column the amount due column populated 0. When i entered 60 into the actual column, i wanted it to subtract 60 from 400 (the value in the expected column) and change the value in the amount due column to 340. If possible i want the amount due to be able do both things. Change when i enter a value into the actual column and change to 0 when i enter paid into the status
column. Hope that makes some more sense.

 

[Joe4]

OK, I think you just need to make one very minor change to your formula:

=IF(E11="paid",0,B11-C11)

Note: If you are seeing "-" in cells where the formula is returning 0, that is due to the fact that you have a Custom format on the cell!
Just change the Format of that column to your desired Number format, and it will resolve that issue for you.

 

[Arts]

OK, I think you just need to make one very minor change to your formula:

=IF(E11="paid",0,B11-C11)

Note: If you are seeing "-" in cells where the formula is returning 0, that is due to the fact that you have a Custom format on the cell!
Just change the Format of that column to your desired Number format, and it will resolve that issue for you.

Just on the zero showing, is this also not a feature that can be selected

File>Options>Advanced, under section "Display options for this worksheet" (below) highlighted in yellow

 

[Joe4]

Just on the zero showing, is this also not a feature that can be selected

File>Options>Advanced, under section "Display options for this worksheet" (below) highlighted in yellow


View attachment 82131

Good point. It could either be that or a Custom Format.

 

[biggynbp]

It was the custom format!

 

[biggynbp]

Thank yo guys from the bottom of my heart.....This is awesome!!

 

[Joe4]

You are welcome.
So does my formula in post 4 do exactly what you need?

 

[biggynbp]

Yes it does!!! it was so simple.