OneGuyFromBarlick

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

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

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.

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

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.

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 ?

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.

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.

Sorry, I find this thread totally incomprehensible. I must be deficient!

(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

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.

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 ).

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.

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.

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!

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

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.