1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Help Required

Discussion in 'HTML, Graphics & Programming' started by byeo, Aug 1, 2018.

  1. byeo

    Wise Guy

    Joined: Sep 11, 2013

    Posts: 2,229

    Location: Sheffield

    I know next to nothing with Excel but want to have a simple chart that I can use to track my savings over a set period of time and also see how close percentage wise I am to the goal.

    I have looked online for something similar but can't narrow it down to my specific requests.

    I'm wanting to save £300 p/m from July 2018 to August 2020 = £7800, there may be months where we save more and months where we save less but eventually it'll even out. Could someone be kind enough to tell me what formulas I need or even knock a quick sheet up for me if it doesn't take too long?

    I'd be extremely grateful.

    Thanks.
     
  2. Greboth

    Mobster

    Joined: Aug 6, 2010

    Posts: 4,387

    Location: Land of make believe

    I'm assuming that you want to see a per month breakdown to see when you are below or above target as well as total.
    Unfortunately I can't upload one for you but it isn't complicated so hopefully you can follow the below.

    In cell B1 type Jul-18, C1 type Aug-18, D1 Sept-18 then drag this across to cell AA2 which should be Aug-20.
    In cell A2 type Target and then £300 under each date
    In cell A3 type Value Saved
    In AB2 type =SUMIF(B1:AA1,"<"&TODAY(),B2:AA2)
    In AB3 type =SUMIF(B1:AA1,"<"&TODAY(),B3:AA3)

    The formula in AB2 will add up your target to todays date and AB3 will add up what you have saved to todays date. If you want your percentage to target to date then that would simply be =AB3/AB2.

    You still may have to do a little playing around with the "<"&TODAY() bit of the formulas depending on when in the month you will be recording the saving. As default it will be the first of each month, so tomorrow it will then also include August's savings.

    Edit: For the date part, if for example you won't be paying your saving in to whatever account until the 15th of each month then you can change it to "<"&Today()-15. Therefore each months target value's won't be included in the total until the 15th of the month. Obviously change the -15 to -10 for the 10th, -22 for the 22nd etc.
     
    Last edited: Aug 1, 2018
  3. byeo

    Wise Guy

    Joined: Sep 11, 2013

    Posts: 2,229

    Location: Sheffield

    Thanks, I'll have a crack at putting this together now.