+ Reply to Thread
Results 1 to 3 of 3

Calculate Series SUM

  1. #1
    Registered User
    Join Date
    08-14-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Calculate Series SUM

    Hi everyone,

    I try to get a series sum calculated in excel which is as follows:

    IMG_1457.jpg


    Any idea this can be solved thru a formula or function?

    Much appreciated

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Calculate Series SUM

    Can you supply (on an excel sheet) the individual values and the value of the constant which make the final answer?

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,453

    Re: Calculate Series SUM

    I'll go for the low hanging fruit. Here's one way I might do this:

    0) I was unsure from you picture whether the second term was a stand alone term or whether it was included in the summation. For the example purposes, I have assumed it is a standalone term [equivalent to 1000/(1+k)^30+SUM(for t=1 to 30)(47.5/(1+k)^t)]
    1) Enter the individual terms in the summation in a column. Something like =47.5/$C$2^ROW(A1) copied down 30 rows. C2 contains the 1+k value (it looks like something close to 1.055 is the correct value for 1+k to get 891).
    2) Enter a SUM() function to get the summation. =SUM(A1:A30,1000/$C$2^30) [assuming I entered the formula from step 1 in A1 and copied to A30].

    I cannot be sure, but the picture suggests that the next step is to find the value for 1+k that yields the desired target value (891). There is no way to solve this function for k, so one needs to use numeric methods. This is most easily done using Excel's built Goal Seek or Solver utilities.

    3) (Optional) Enter 891 into a convenient cell. Enter =(cell with 891)-(cell with summation from step 2). I like to enter this step, because it becomes easier for future problems simply enter a new target for 891 and execute the Solver algorithm again.
    4) Call Solver (or Goal Seek, but I prefer Solver) and tell it to
    4a) Set target cell: Helper cell from step 3.
    4b) To value of: 0
    4c) By changing: C2 (cell with 1+k value).

    That would be one way to set this up. Did I interpret the problem correctly?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate a series number
    By wiewie002 in forum Excel General
    Replies: 21
    Last Post: 01-09-2017, 06:20 AM
  2. Calculate a series number
    By wiewie002 in forum Excel General
    Replies: 1
    Last Post: 12-30-2016, 12:56 PM
  3. Calculate a series number
    By wiewie002 in forum Excel General
    Replies: 1
    Last Post: 12-30-2016, 12:52 PM
  4. [SOLVED] How Can I Calculate a Series of Row's Totals?
    By Sandcastle in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-07-2014, 02:14 PM
  5. Can I use Formulas to Calculate Chart Series?
    By sasquatchbill in forum Excel General
    Replies: 6
    Last Post: 08-14-2006, 02:25 PM
  6. [SOLVED] Calculate terms in a Series
    By BarbV in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-03-2005, 11:05 PM
  7. How to calculate NPV of an infinite series?
    By Mike in forum Excel General
    Replies: 2
    Last Post: 09-15-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1