Author Topic: excel 2007 running balance  (Read 1032 times)

Offline bodger

  • RootsChat Aristocrat
  • ******
  • Posts: 1,035
  • bodger sen. jun.
    • View Profile
excel 2007 running balance
« 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
Attenborough, Bacon,Melbourne, Thorpe, Ride,Simpson/ Derbyshire, Judson,Bacon,/Keighley,
Lockett/ Manchester, Harling/ Lancaster & Manchester

Offline StevieSteve

  • RootsChat Aristocrat
  • ******
  • Posts: 1,679
    • View Profile
Re: excel 2007 running balance
« Reply #1 on: Tuesday 12 September 17 13:23 BST (UK) »
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!
Middlesex: KING,  MUMFORD, COOK, ROUSE, GOODALL, BROWN
Oxford: MATTHEWS, MOSS
Kent: SPOONER, THOMAS, KILLICK, COLLINS
Cambs: PRIGG, LEACH
Hants: FOSTER
Montgomery: BREES
Surrey: REEVE

Offline bodger

  • RootsChat Aristocrat
  • ******
  • Posts: 1,035
  • bodger sen. jun.
    • View Profile
Re: excel 2007 running balance
« Reply #2 on: Tuesday 12 September 17 15:05 BST (UK) »
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
Attenborough, Bacon,Melbourne, Thorpe, Ride,Simpson/ Derbyshire, Judson,Bacon,/Keighley,
Lockett/ Manchester, Harling/ Lancaster & Manchester

Offline HughC

  • RootsChat Veteran
  • *****
  • Posts: 934
  • et patribus et posteritati
    • View Profile
Re: excel 2007 running balance
« Reply #3 on: Tuesday 12 September 17 15:08 BST (UK) »
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" !
Bagwell of Kilmore & Lisronagh, Co. Tipperary;  Beatty from Enniskillen;  Brown from Preston, Lancs.;  Burke of Ballydugan, Co. Galway;  Casement in the IoM and Co. Antrim;  Davison of Knockboy, Broughshane;  Frobisher;  Guillemard;  Harrison in Co. Antrim and Dublin;  Jones around Burton Pedwardine, Lincs.;  Lindesay of Loughry;  Newcomen of Camlagh, Co. Roscommon;  Shield;  Watson from Kidderminster;  Wilkinson from Leeds


Offline StevieSteve

  • RootsChat Aristocrat
  • ******
  • Posts: 1,679
    • View Profile
Re: excel 2007 running balance
« Reply #4 on: Tuesday 12 September 17 15:18 BST (UK) »
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   *  )
Middlesex: KING,  MUMFORD, COOK, ROUSE, GOODALL, BROWN
Oxford: MATTHEWS, MOSS
Kent: SPOONER, THOMAS, KILLICK, COLLINS
Cambs: PRIGG, LEACH
Hants: FOSTER
Montgomery: BREES
Surrey: REEVE

Offline hallmark

  • ~
  • RootsChat Marquessate
  • ****
  • Posts: 17,525
    • View Profile
Re: excel 2007 running balance
« Reply #5 on: Tuesday 12 September 17 17:06 BST (UK) »
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??
Give a man a record and you feed him for a day.
Teach a man to research, and you feed him for a lifetime.

Offline StevieSteve

  • RootsChat Aristocrat
  • ******
  • Posts: 1,679
    • View Profile
Re: excel 2007 running balance
« Reply #6 on: Tuesday 12 September 17 17:14 BST (UK) »
Think of it as a bank account

add the deposits, take away the costs = balance

100+100-50-60 = 90

Ta-da
Middlesex: KING,  MUMFORD, COOK, ROUSE, GOODALL, BROWN
Oxford: MATTHEWS, MOSS
Kent: SPOONER, THOMAS, KILLICK, COLLINS
Cambs: PRIGG, LEACH
Hants: FOSTER
Montgomery: BREES
Surrey: REEVE

Offline hallmark

  • ~
  • RootsChat Marquessate
  • ****
  • Posts: 17,525
    • View Profile
Re: excel 2007 running balance
« Reply #7 on: Tuesday 12 September 17 17:15 BST (UK) »
Ahhh...your 90 would be      =(B2+B3)-(G2+G3)  in Cell H3
Give a man a record and you feed him for a day.
Teach a man to research, and you feed him for a lifetime.

Online KGarrad

  • RootsChat Marquessate
  • *******
  • Posts: 26,064
  • Census information Crown Copyright, from www.nationalarchives.gov.uk
    • View Profile
Re: excel 2007 running balance
« Reply #8 on: Tuesday 12 September 17 17:16 BST (UK) »
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?)
Garrad (Suffolk, Essex, Somerset), Crocker (Somerset), Vanstone (Devon, Jersey), Sims (Wiltshire), Bridger (Kent)