I am working on a spreadsheet that will allow me to keep track of my savings
account. I have a set number ($600.00) that I need to place in my checking
per week that will cover my budget. When I work overtime, I would like to
place the difference into savings. Should be simple enough (the value in the
savings block should = the value entered into the wages block-600.00). Where
it becomes complicated for me is, sometimes I may fall under 600.00 wages.
This would create a negative number in the previous weeks savings block. When
I calculate it for the next week, I would like to be able to add back to the
checking the negative amount from the previous week before taking out for the
savings. I probably have lost you by now. Here is an example: Week 1 wages =
$540.00. This would show up as a -$40.00 to my savings account for week 1.
Week 2 wages = $700.00. This would show up as a $100.00 to my savings account
for week 2. Here is what I would like for this to do: Week 2 wages = $700.00
- $600.00 = $100.00 + - $40.00 = $60.00 to my savings account for week 2.
This tells me how much to transfer from checking to savings every week in
order to keep my $600.00 constant for my budget. It looks like I need an IF
statement with a value for true and a value for false. I can't seem to figure
out how to build the formula. Any HELP would be greatly appreciated. Thanks,
Rodney

I think this will do it for you; assumes week 1 entry is on row 2, so this
formula would go into row 3 (and fill down). Column A = your take home
check, column B = amount to savings.
In B3
=IF(AND(B2<0,A3600),A3-600+B2,A3-600)
With the 2 examples you gave I get

A B
1 INCOME SAVINGS
2 540 -60
3 700 40


"Rodney" wrote:

I am working on a spreadsheet that will allow me to keep track of my savings
account. I have a set number ($600.00) that I need to place in my checking
per week that will cover my budget. When I work overtime, I would like to
place the difference into savings. Should be simple enough (the value in the
savings block should = the value entered into the wages block-600.00). Where
it becomes complicated for me is, sometimes I may fall under 600.00 wages.
This would create a negative number in the previous weeks savings block. When
I calculate it for the next week, I would like to be able to add back to the
checking the negative amount from the previous week before taking out for the
savings. I probably have lost you by now. Here is an example: Week 1 wages =
$540.00. This would show up as a -$40.00 to my savings account for week 1.
Week 2 wages = $700.00. This would show up as a $100.00 to my savings account
for week 2. Here is what I would like for this to do: Week 2 wages = $700.00
- $600.00 = $100.00 + - $40.00 = $60.00 to my savings account for week 2.
This tells me how much to transfer from checking to savings every week in
order to keep my $600.00 constant for my budget. It looks like I need an IF
statement with a value for true and a value for false. I can't seem to figure
out how to build the formula. Any HELP would be greatly appreciated. Thanks,
Rodney

Hi,
see the example

Target to Chequing 600
Salary C Acc B Owed Savings Acc
Week1 540 540 60 0
Week2 700 600 0 40
Week3 800 600 0 200
Week4 350 350 250 0
Week5 850 600 0 0

column C= transfers to chequing
column D= Balance owed to chequing if salary less than 600

in cell B1 enter your target to chequing
in column B enter your salary
in cell C3 enter
=IF(B3<$B$1,B3,$B$1)

in cell D3 enter

=+$B$1-C3

in cell E3 enter

=+B3-C3
in cell E4 enter and copy down
=+B4-C4-D3






"Rodney" wrote:

I am working on a spreadsheet that will allow me to keep track of my savings
account. I have a set number ($600.00) that I need to place in my checking
per week that will cover my budget. When I work overtime, I would like to
place the difference into savings. Should be simple enough (the value in the
savings block should = the value entered into the wages block-600.00). Where
it becomes complicated for me is, sometimes I may fall under 600.00 wages.
This would create a negative number in the previous weeks savings block. When
I calculate it for the next week, I would like to be able to add back to the
checking the negative amount from the previous week before taking out for the
savings. I probably have lost you by now. Here is an example: Week 1 wages =
$540.00. This would show up as a -$40.00 to my savings account for week 1.
Week 2 wages = $700.00. This would show up as a $100.00 to my savings account
for week 2. Here is what I would like for this to do: Week 2 wages = $700.00
- $600.00 = $100.00 + - $40.00 = $60.00 to my savings account for week 2.
This tells me how much to transfer from checking to savings every week in
order to keep my $600.00 constant for my budget. It looks like I need an IF
statement with a value for true and a value for false. I can't seem to figure
out how to build the formula. Any HELP would be greatly appreciated. Thanks,
Rodney

JLatham,

Lines 2 & 3 worked correctly. I fillled down. I even copied the formula and
pasted into the rest of the column. I could not get the others to work
correctly. Any ideas what I need to do to correct this problem? I keep
getting a "Circular Reference Warning" also.

Thanks for your time....Rodney


"JLatham" wrote:

I think this will do it for you; assumes week 1 entry is on row 2, so this
formula would go into row 3 (and fill down). Column A = your take home
check, column B = amount to savings.
In B3
=IF(AND(B2<0,A3600),A3-600+B2,A3-600)
With the 2 examples you gave I get

A B
1 INCOME SAVINGS
2 540 -60
3 700 40


"Rodney" wrote:

I am working on a spreadsheet that will allow me to keep track of my savings
account. I have a set number ($600.00) that I need to place in my checking
per week that will cover my budget. When I work overtime, I would like to
place the difference into savings. Should be simple enough (the value in the
savings block should = the value entered into the wages block-600.00). Where
it becomes complicated for me is, sometimes I may fall under 600.00 wages.
This would create a negative number in the previous weeks savings block. When
I calculate it for the next week, I would like to be able to add back to the
checking the negative amount from the previous week before taking out for the
savings. I probably have lost you by now. Here is an example: Week 1 wages =
$540.00. This would show up as a -$40.00 to my savings account for week 1.
Week 2 wages = $700.00. This would show up as a $100.00 to my savings account
for week 2. Here is what I would like for this to do: Week 2 wages = $700.00
- $600.00 = $100.00 + - $40.00 = $60.00 to my savings account for week 2.
This tells me how much to transfer from checking to savings every week in
order to keep my $600.00 constant for my budget. It looks like I need an IF
statement with a value for true and a value for false. I can't seem to figure
out how to build the formula. Any HELP would be greatly appreciated. Thanks,
Rodney