Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need simple IF formula, please | Excel Worksheet Functions | |||
Simple Formula Help | Excel Worksheet Functions | |||
Formula--rather simple one | Excel Discussion (Misc queries) | |||
help with simple formula | Excel Worksheet Functions | |||
simple formula | Excel Worksheet Functions |