Simple formula
 

Hi


What formula do I need to use to create a running balance in my budget
spreadsheet i.e. I have 4 columns A = Name, B = Period Actual spend, C =
Period budget spend, D = Year to date actual spend. So I want to be able to
enter a value into B at the end of every month and D will automatically
change.

P.s. I'm a novice so I hope I explained myself ok?


--
Deb

Simple formula
 

Hey Deb
In D

"Debbie" wrote:

Hi, I am assuming that you have 4 cells (A2,B2,C2,D2 as A1,B1,C1,D1 are Name, Period Actual Spend, Period Budget Spend, and Year to date actual spend). Click on D2 and type in "=B2". This will make D whatever B is. If you don't want that (for example if you want D to reflect B+C than in D2 you type in "=B2+C2".


What formula do I need to use to create a running balance in my budget
spreadsheet i.e. I have 4 columns A = Name, B = Period Actual spend, C =
Period budget spend, D = Year to date actual spend. So I want to be able to
enter a value into B at the end of every month and D will automatically
change.

P.s. I'm a novice so I hope I explained myself ok?


--
Deb

Simple formula
 

One way:

Assuming headings in row 1 and data starting in row 2:

D2: =IF(B2<"",B2,"")
D3: =IF(B3<"",D2+B3,"")

Copy D3 down as far as required.


In article ,
Debbie wrote:

Hi


What formula do I need to use to create a running balance in my budget
spreadsheet i.e. I have 4 columns A = Name, B = Period Actual spend, C =
Period budget spend, D = Year to date actual spend. So I want to be able to
enter a value into B at the end of every month and D will automatically
change.

P.s. I'm a novice so I hope I explained myself ok?

Simple formula
 

Hi

Sorry dont think I explained myself clear enough I had tried that I will try
to duplicate below
B2 C2 D2 E2 F2 G2
H2
Name Act Bud variance YTD Act YTD Bud
YTD variance
Stationary 265.33 350.00 84.67 3,450.34 3,211.38 -238.96

I want to be able to enter a new total into C2 every month and for it to
automatically update F2. I started this spreadsheet half way through the
year and so in F2 I originally put =3194.01+C2 but now I realise that I
would have to change this every month as well and as I have 74 rows it would
take to long If you can help further I would be grateful

Thanks

Deb

--
Deb


"Debbie" wrote:

Hi


What formula do I need to use to create a running balance in my budget
spreadsheet i.e. I have 4 columns A = Name, B = Period Actual spend, C =
Period budget spend, D = Year to date actual spend. So I want to be able to
enter a value into B at the end of every month and D will automatically
change.

P.s. I'm a novice so I hope I explained myself ok?


--
Deb

Simple formula
 

Debbie,

It's unclear what you're trying to do. Using your example is it that last
month the YTD Act was 3194.01. This month you've got another 265.33 so you
want the YTD Act to be 3459.34 -- then... next month you want to lock in the
3459.34, put another number in C2 and get a new total? That is, F2 has to
save the running total, but each month the detail making up that running
total is lost?

If that's what you want to do, I'd advise against it. Consider what would
happen if you type in a number in error and then need to correct it. The
total would just continue to rise. Perhaps I don't understand what you're
looking for.

If that's what you want, I think it would require a macro.

Other approaches would be to have a series of 12 tabs, one for each month.
Or instead a new workbook, copied originally from the previous month's
workbook.



"Debbie" wrote:

Hi

Sorry dont think I explained myself clear enough I had tried that I will try
to duplicate below
B2 C2 D2 E2 F2 G2
H2
Name Act Bud variance YTD Act YTD Bud
YTD variance
Stationary 265.33 350.00 84.67 3,450.34 3,211.38 -238.96

I want to be able to enter a new total into C2 every month and for it to
automatically update F2. I started this spreadsheet half way through the
year and so in F2 I originally put =3194.01+C2 but now I realise that I
would have to change this every month as well and as I have 74 rows it would
take to long If you can help further I would be grateful

Thanks

Deb

--
Deb


"Debbie" wrote:

Hi


What formula do I need to use to create a running balance in my budget
spreadsheet i.e. I have 4 columns A = Name, B = Period Actual spend, C =
Period budget spend, D = Year to date actual spend. So I want to be able to
enter a value into B at the end of every month and D will automatically
change.

P.s. I'm a novice so I hope I explained myself ok?


--
Deb

Simple formula
 

I always advise against using an accumulator, which is what you've
described, because a single error in data entry can cause an
unrecoverable loss of accuracy.

But if you insist:

http://www.mcgimpsey.com/excel/accumulator.html


In article ,
Debbie wrote:

Hi

Sorry dont think I explained myself clear enough I had tried that I will try
to duplicate below
B2 C2 D2 E2 F2 G2
H2
Name Act Bud variance YTD Act YTD Bud
YTD variance
Stationary 265.33 350.00 84.67 3,450.34 3,211.38 -238.96

I want to be able to enter a new total into C2 every month and for it to
automatically update F2. I started this spreadsheet half way through the
year and so in F2 I originally put =3194.01+C2 but now I realise that I
would have to change this every month as well and as I have 74 rows it would
take to long If you can help further I would be grateful

Thanks

Deb

Simple formula
 

Thanks, What you describe is exactly what I am trying to do Thanks for your
advise. I will copy the sheet into a new one every month I think.
--
Deb


"Art" wrote:

Debbie,

It's unclear what you're trying to do. Using your example is it that last
month the YTD Act was 3194.01. This month you've got another 265.33 so you
want the YTD Act to be 3459.34 -- then... next month you want to lock in the
3459.34, put another number in C2 and get a new total? That is, F2 has to
save the running total, but each month the detail making up that running
total is lost?

If that's what you want to do, I'd advise against it. Consider what would
happen if you type in a number in error and then need to correct it. The
total would just continue to rise. Perhaps I don't understand what you're
looking for.

If that's what you want, I think it would require a macro.

Other approaches would be to have a series of 12 tabs, one for each month.
Or instead a new workbook, copied originally from the previous month's
workbook.



"Debbie" wrote:

Hi

Sorry dont think I explained myself clear enough I had tried that I will try
to duplicate below
B2 C2 D2 E2 F2 G2
H2
Name Act Bud variance YTD Act YTD Bud
YTD variance
Stationary 265.33 350.00 84.67 3,450.34 3,211.38 -238.96

I want to be able to enter a new total into C2 every month and for it to
automatically update F2. I started this spreadsheet half way through the
year and so in F2 I originally put =3194.01+C2 but now I realise that I
would have to change this every month as well and as I have 74 rows it would
take to long If you can help further I would be grateful

Thanks

Deb

--
Deb


"Debbie" wrote:

Hi


What formula do I need to use to create a running balance in my budget
spreadsheet i.e. I have 4 columns A = Name, B = Period Actual spend, C =
Period budget spend, D = Year to date actual spend. So I want to be able to
enter a value into B at the end of every month and D will automatically
change.

P.s. I'm a novice so I hope I explained myself ok?


--
Deb

Simple formula
 

Thanks I now understand. I have vsited your web page and as a novice I think
it best if I copy the sheet each month into a new one as previously advised.
Cheers

--
Deb


"JE McGimpsey" wrote:

I always advise against using an accumulator, which is what you've
described, because a single error in data entry can cause an
unrecoverable loss of accuracy.

But if you insist:

http://www.mcgimpsey.com/excel/accumulator.html


In article ,
Debbie wrote:

Hi

Sorry dont think I explained myself clear enough I had tried that I will try
to duplicate below
B2 C2 D2 E2 F2 G2
H2
Name Act Bud variance YTD Act YTD Bud
YTD variance
Stationary 265.33 350.00 84.67 3,450.34 3,211.38 -238.96

I want to be able to enter a new total into C2 every month and for it to
automatically update F2. I started this spreadsheet half way through the
year and so in F2 I originally put =3194.01+C2 but now I realise that I
would have to change this every month as well and as I have 74 rows it would
take to long If you can help further I would be grateful

Thanks

Deb