RootsChat.Com
General => Technical Help => Topic started by: bodger on Tuesday 12 September 17 11:38 BST (UK)
-
Any one there to help ?,
date deposit date product quantity cost total balance
100 toy 5 10 50 50
100 bug 12 5 60 90
50 wig 2 100 200 60-
150 toy 2 10 20 70
The above gives an idea of what i am looking for as a formulae, note i'm a novice at the excel use. thank you, bodger
-
Assuming your titles are in row 1
and
Column B is deposit
Column G is total
Column H is Balance
Leave Row 2 blank
In cell H3 type =B3-G3+H2
Copy or drag it down the whole of Column H
Voila!
-
A little more clarity
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
Steve, I would like the formulae to include columns E, F, G calculated
row 2 would be :-
2 100 - (E2, xF2, =G2)= H2
3 100 (H2,+B3)-(E3 xF3)=H3
4 50 (H3 +B4)-(E4xF4) = H4
This is how in my little brain it would appear, i know it does n't conform to excel protocol, thats why i was enquiringly about formula's
Thank you for replies bodger
6
-
As Steve has shown, you turn your formula round: leave out your right-hand side (because you enter it in the cell with that designation), and start with the equals sign.
Not appropriate here, but what novices may not have learned is that if you precede a column letter or row number with $, it remains unchanged when you copy or drag. That can be useful.
Oh, and ther bain't no such thing as "a formulae" !
-
So, Bodger, with my H3 example and wee Hugh's explanation let us know what you would type in cell G2 (The Excel symbol for multiplication is * )
-
row 2 would NOT be :-
2 100 - (E2, xF2, =G2)= H2
IN G2 you type =(E2xF2)
As for 100 bug 12 5 60 90 what is the 90 meant be be?? Where does the 90 come from??
-
Think of it as a bank account
add the deposits, take away the costs = balance
100+100-50-60 = 90
Ta-da
-
Ahhh...your 90 would be =(B2+B3)-(G2+G3) in Cell H3
-
Column B shows money in - a deposit.
Columns D, E and F show expenditure, with a total in column G.
Column H is a running total.
So, row 2 shows 100 in, less 50 spent, for a balance of 50.
And row 3, shows another 100 in, less 60 spent. That's 40 profit, added to the previous 50 = 90!
QED!
(Took me a while to work it out? Rather a strange way of working, I feel?)
-
Think of it as a bank account
add the deposits, take away the costs = balance
100+100-50-60 = 90
Ta-da
Yes but you have to use brackets in correct places,,, as in
(100+100)-(50-60) = 90
-
Yes but you have to use brackets in correct places,,, as in
(100+100)-(50+60) = 90
(Deposit+Deposit)-(Spend+Spend)=90
-
Posts: 939
bodger sen. jun.
View Profile Personal Message (Offline)
Re: excel 2007 running balance
« Reply #2 on: Today at 03:05:03 PM »
Quote
A little more clarity
A B C D E F G H
1 date deposit date product quantity cost total balance
2 100 toy 5 x 10 = 50 =(E2xF2) = 50 formula =B2-G2
3 100 bug 12 x 5 = 60 = 90 formula =(H2+B3)-(G2+G3)
4 50 wig 2 x 100 = 200 = 60-
5 150 toy 2 x 10 = 20 = 70
-
Online hallmark
-
RootsChat Marquessate
******
Posts: 6,158
View Profile Personal Message (Online)
Re: excel 2007 running balance
« Reply #11 on: Today at 05:46:15 PM »
Quote
Posts: 939
bodger sen. jun.
View Profile Personal Message (Offline)
Re: excel 2007 running balance
« Reply #2 on: Today at 03:05:03 PM »
Quote
A little more clarity
A B C D E F G H
1 date deposit date product quantity cost total balance
2 100 toy 5 x 10 =(E2xF2) formula =B2-G2
3 100 bug 12 x 5 =(E3xF3) formula =(H2+B3)-(G2+G3)
-
That last formula isn't right - you need to lose the G2 (it's already included in calculating H2)
(Which will then have the added bonus of being the same as I had already posted in the first reply on this thread)
-
That last formula isn't right - you need to lose the G2 (it's already included in calculating H2)
(Which will then have the added bonus of being the same as I had already posted in the first reply on this thread)
Yes you are correct
A B C D E F G H
1 date deposit date product quantity cost total balance
2 100 toy 5 x 10 =(E2xF2) formula =B2-G2
3 100 bug 12 x 5 =(E3xF3) formula =(H2+B3)-G3
don't have Excel...