Running balance in 2007 excel

Post Reply
User avatar
Bodger
Avid User
Posts: 946
Joined: 23 Jan 2012, 12:30
Location: Ireland

Running balance in 2007 excel

Post by Bodger » 12 Sep 2017, 16:10

I am looking to enter a formula to carry out a running balance on a sheet as below. These events and products can occur at various times and i would like to set up a sheet so that any one entering information can keep the account to date. Thanks bodger




A B C D E F G H
1 date deposit date product quantity cost total balance
2 100 toy 5 x 10 = 50 = 50
3 100 bug 12 x 5 = 60 = 90
4 50 wig 2 x 100 = 200 = 60-
5 150 toy 2 x 10 = 20 = 70

User avatar
Wendyf
Global Moderator
Global Moderator
Posts: 4406
Joined: 23 Jan 2012, 12:26
Location: Lower Burnt Hill, looking out over Barlick

Re: Running balance in 2007 excel

Post by Wendyf » 12 Sep 2017, 16:42

Struggling to grasp what you are trying to achieve from those figures Bodge, can you do a screen shot of the spreadsheet?

User avatar
PanBiker
Site Administrator
Site Administrator
Posts: 6546
Joined: 23 Jan 2012, 13:07
Location: Barnoldswick - In the West Riding of Yorkshire, always was, always will be.

Re: Running balance in 2007 excel

Post by PanBiker » 12 Sep 2017, 17:34

I have one that I setup to show an average that is displayed in a cell at the top. It is calculated from entries for systolic diastolic and pulse readings.

I have another that shows a running total of my earnings from my part time work. Columns for date, hours worked, pay rate total hours for day, total pay for day. Then running totals for hours and pay at the top.

You just need to set the columns up correctly and put the right range formula in to calculate your total.

If you could put an image up of what you want it would help.
Ian

User avatar
Bodger
Avid User
Posts: 946
Joined: 23 Jan 2012, 12:30
Location: Ireland

Re: Running balance in 2007 excel

Post by Bodger » 12 Sep 2017, 17:48

Got a screen shot but cannot find how to post it ?

plaques
Donor
Posts: 2080
Joined: 23 May 2013, 22:09

Re: Running balance in 2007 excel

Post by plaques » 12 Sep 2017, 17:56

Bodger, You look to be trying to do several different things at one go.

If I understand it you have multiple types of toys etc which you want to list in the sequence they were ordered in. These would be recognized and added together to give a total price of each type.
So the programme has to be smart enough to look back up the list, find the same type of toy, and add them together to make a running total for that type.

Sorry its beyond me. If you had only a limited number of toy types you could create a column for each type then it would be a lot easier.

User avatar
Bodger
Avid User
Posts: 946
Joined: 23 Jan 2012, 12:30
Location: Ireland

Re: Running balance in 2007 excel

Post by Bodger » 12 Sep 2017, 18:24

A B C D E F G H
1 date deposit date product quantity price total balance
2 100 5 times 10 = 50 leaves50
3 100 12 5 60 90
4 60 2 100 200 -50
5 150 2 10 20 80
6
7
8
Line 2 B2 -(E2*F2}
line 3 H2+B3 - (E3*F33)
line 4 H3+B4 -(E4*F4)
line 5 H4+B5-(E5*F5)
If possible i would like to apply the formula down the sheet ?

plaques
Donor
Posts: 2080
Joined: 23 May 2013, 22:09

Re: Running balance in 2007 excel

Post by plaques » 12 Sep 2017, 18:58

Sorry Bodger, at first I was simply confused. Now I'm totally confused.

Don't understand the 'Leaves' bit. and also why we get negative numbers. Then comes (E3*F33) where did F33 come from?

What is the first number in your lists. ie: 100, 100, 60, 150. Are they identifying something?
I think its just a matter of understanding what you are trying to do.

User avatar
PanBiker
Site Administrator
Site Administrator
Posts: 6546
Joined: 23 Jan 2012, 13:07
Location: Barnoldswick - In the West Riding of Yorkshire, always was, always will be.

Re: Running balance in 2007 excel

Post by PanBiker » 12 Sep 2017, 20:17

You should be able to replicate a formula down the column by simply dragging (pick the cell up at the lower right corner). Dragging with SHIFT held down or CTRL will have different effects on the formula copied. and whether it is relational to adjacent cells. Try with a simple sheet and some dummy data to see the different effects.

If you have saved your screen shot you can add the file as an attachment. Click the attachment tab at the bottom of the post editor and click "add files" navigate to your saved file and attach to the post. You can then insert the file into the post at the cursor position or drag and drop it into the edit window.
Ian

User avatar
Stanley
Global Moderator
Global Moderator
Posts: 37698
Joined: 23 Jan 2012, 12:01
Location: Barnoldswick. Nearer to Heaven than Gloria.

Re: Running balance in 2007 excel

Post by Stanley » 13 Sep 2017, 02:53

Sorry, I find this thread totally incomprehensible. I must be deficient!
Stanley Challenger Graham
Stanley's View
scg1936 at talktalk.net

"Beware of certitude" (Jimmy Reid)
The floggings will continue until morale improves!

User avatar
Bodger
Avid User
Posts: 946
Joined: 23 Jan 2012, 12:30
Location: Ireland

Re: Running balance in 2007 excel

Post by Bodger » 13 Sep 2017, 06:46

(Sorry its beyond me. If you had only a limited number of toy types you could create a column for each type then it would be a lot easier.)
Plaques, this would be an answer. Thanks, bodger

plaques
Donor
Posts: 2080
Joined: 23 May 2013, 22:09

Re: Running balance in 2007 excel

Post by plaques » 13 Sep 2017, 07:21

Bodger, This almost kept me awake all night, I came to the conclusion you were trying to keep a 'stock' balance at each entry. In which case you may have a row with an entry that only adds some extra stock against the toy type. Must brush up on these spread sheets thingies.

User avatar
Wendyf
Global Moderator
Global Moderator
Posts: 4406
Joined: 23 Jan 2012, 12:26
Location: Lower Burnt Hill, looking out over Barlick

Re: Running balance in 2007 excel

Post by Wendyf » 13 Sep 2017, 07:50

Why not split it into 2 sheets, one for stock control and one for accounting. You can refer to cells in either sheet in formulas (or formulae in case any pedantic spell checkers are watching :extrawink: ).

User avatar
PanBiker
Site Administrator
Site Administrator
Posts: 6546
Joined: 23 Jan 2012, 13:07
Location: Barnoldswick - In the West Riding of Yorkshire, always was, always will be.

Re: Running balance in 2007 excel

Post by PanBiker » 13 Sep 2017, 08:56

Looks like you only need 7 columns:

Across the sheet A to G

A B C D E F G

Date, Deposit Date, Product Description, Quantity, Unit Price, Sub Total and Total.

Format columns:

A Date
B Date
C Text
D Number
E Number
F Number
G Number

If column G is just an overall total you can put this in any cell on the sheet for convenience

I would set al numerical fields to be 2 decimal places.

Assuming you put titles on row 1 then a space, formulas will start on row 3. You will only need 2 formulas, Sub Total and Total

F3 would be (D3xE3) copy this relationally down the sheet by dragging (D4xE4), (D5xE5) etc.. This will produce all your Sub Totals in column F.

In G3 or whatever cell you decide to put your total in. Total column F down the sheet from F3 to cover the range of your data. You can make the range go past where your rows of data end to cater for additional entries.

i.e in G3 you would use the formula =SUM(F3:F150) this would add all values in column F for 147 rows down the sheet. Easily extendible if your sheet grows.

Is this something like what you need? It's a lot harder to explain than actually do.
Ian

User avatar
PanBiker
Site Administrator
Site Administrator
Posts: 6546
Joined: 23 Jan 2012, 13:07
Location: Barnoldswick - In the West Riding of Yorkshire, always was, always will be.

Re: Running balance in 2007 excel

Post by PanBiker » 13 Sep 2017, 13:24

I forgot to say that you could add your spreadsheet as an attachment also. That would allow us to download it and have a look at it.
Ian

User avatar
Stanley
Global Moderator
Global Moderator
Posts: 37698
Joined: 23 Jan 2012, 12:01
Location: Barnoldswick. Nearer to Heaven than Gloria.

Re: Running balance in 2007 excel

Post by Stanley » 14 Sep 2017, 02:59

Oh please do that! This thread is keeping a lot of people from hanging about on street corners and is doubtless good for countering the onset of dementia!
Stanley Challenger Graham
Stanley's View
scg1936 at talktalk.net

"Beware of certitude" (Jimmy Reid)
The floggings will continue until morale improves!

User avatar
Bodger
Avid User
Posts: 946
Joined: 23 Jan 2012, 12:30
Location: Ireland

Re: Running balance in 2007 excel

Post by Bodger » 14 Sep 2017, 08:02

Problem solved thanks Plaques
Stanley, Excel is a graph where No's. refer to lines across & columns are referred to in A<B<C> etc so a formula could be expressed as F3 + E4 - A1
My solution from Plaques suggestion
E3"cost" * F3 "qnty" Formula placed in G4 , followed by second formula
H3 "balance" + B4 "deposit" - G4, formula in H4
Note * represents multiply
I was trying to do it all in one formula !
Plaques re "leaves" this dates back to Miss Lockwood in 1944 @ Hepworth School I was using the 3 rs.
ie. Six take away four LEAVES two
I now realize people today may think that i was referring to fast food joints and
marijuana
Even though i'm in my eighties i still try and learn, especially from you youngsters who grew up with calculators & computers, we had to make do with an abacus, ink wells, and if lucky a slide rule, i'm still not too proud to ask for help even though i may give the impression i know it all at times
Thanks again everyone, bodger

User avatar
PanBiker
Site Administrator
Site Administrator
Posts: 6546
Joined: 23 Jan 2012, 13:07
Location: Barnoldswick - In the West Riding of Yorkshire, always was, always will be.

Re: Running balance in 2007 excel

Post by PanBiker » 14 Sep 2017, 09:29

This post does demonstrate how difficult it can be to get clear information across sometimes. It does highlight slight deficiencies in the post editor though. The editor used to have an icon for inserting a table which seems to be missing from this current "enhanced" version! That would have made it a lot easier to get the layout and requirement over.

Regarding the attachment feature, this lends itself perfectly to problems such as this. You can attach virtually any file type to a post although we seem to exclusively use it for attaching and inserting images into posts which is not really the best way for that particular task.
Ian

User avatar
Stanley
Global Moderator
Global Moderator
Posts: 37698
Joined: 23 Jan 2012, 12:01
Location: Barnoldswick. Nearer to Heaven than Gloria.

Re: Running balance in 2007 excel

Post by Stanley » 15 Sep 2017, 04:01

:biggrin2:
Stanley Challenger Graham
Stanley's View
scg1936 at talktalk.net

"Beware of certitude" (Jimmy Reid)
The floggings will continue until morale improves!

Post Reply

Return to “Forums”

Who is online

Users browsing this forum: No registered users