+ Reply to Thread
Results 1 to 10 of 10

Pulling previous value to calculate total

  1. #1
    Registered User
    Join Date
    07-19-2016
    Location
    Williamsburg, Virginia
    MS-Off Ver
    2016
    Posts
    36

    Pulling previous value to calculate total

    Hi all,

    I am trying to create a volume calculation that is built into our template to allow us to see the total volume of work we are programming for our athletes.
    Excel3.jpeg
    What I want is for the percentage to convert to a whole number, multiplied by the reps in column 2, and then added to the subsequent calculations to derive a total volume, such that in this example (50*3)+(60*2)+(65*2)+(65*2) = 530.

    The extra 2 is not a mistake; sometimes we leave a set open for an athlete to move up or down based upon their performance of the previous set. I would like for this value to be factored into the calculation and to be multiplied by the last percentage listed in that column, so in this case, that final set of 2 reps would be multiplied by 65, for a full total of 660 for that entire set of values in the picture. Any solutions?

    We will sometimes have a balanced amount of cells that are filled with percentages and reps, so I know the formula will need to be "if(" based, I'm just not sure how to get the formula to pull the correct percentage and calculate the volume appropriately if the arrays are uneven.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,891

    Re: Pulling previous value to calculate total

    Try this ...

    =SUM(IF(A2:A14="",A1:A13,A2:A14)*B2:B14)*100

    Enter with Ctrl+Shift+Enter.

  3. #3
    Registered User
    Join Date
    07-19-2016
    Location
    Williamsburg, Virginia
    MS-Off Ver
    2016
    Posts
    36

    Re: Pulling previous value to calculate total

    That did not work, I got an "N/A" back running that formula. Unless I misread how you format the formula. The cells I am operating in are C17:D22.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,963

    Re: Pulling previous value to calculate total

    Try this:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-19-2016
    Location
    Williamsburg, Virginia
    MS-Off Ver
    2016
    Posts
    36

    Re: Pulling previous value to calculate total

    That formula worked, thank you bebo. How would I manipulate the formula if I had a sixth line similar to the fifth line, so that the 6th line would also read back to the last percentage cell?

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,963

    Re: Pulling previous value to calculate total

    Mayby Change C17:C22 to C17:C23 for six lines.

  7. #7
    Registered User
    Join Date
    07-19-2016
    Location
    Williamsburg, Virginia
    MS-Off Ver
    2016
    Posts
    36

    Re: Pulling previous value to calculate total

    Allow me to clarify- the cells I am currently working (not including the headings at the top) are C17:C22. C23 would go into another section, so I am looking to utilize a formula that would extend the calculation to a sixth set like is written below, where it would simply add another 2 x 65 = 130 to the total training load.
    Excel4.jpeg

  8. #8
    Registered User
    Join Date
    07-19-2016
    Location
    Williamsburg, Virginia
    MS-Off Ver
    2016
    Posts
    36

    Re: Pulling previous value to calculate total

    Bebo, any solution to my updated need for the formula?

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,963

    Re: Pulling previous value to calculate total

    There will have 2 SUMPRODUCT:

    Please Login or Register  to view this content.
    Is it are you looking for?

  10. #10
    Registered User
    Join Date
    07-19-2016
    Location
    Williamsburg, Virginia
    MS-Off Ver
    2016
    Posts
    36

    Re: Pulling previous value to calculate total

    Perfect! Thank you

+ 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 Total Hours each occurrence and Total Days for all occurences
    By CARD17 in forum Excel Formulas & Functions
    Replies: 47
    Last Post: 01-18-2017, 11:02 PM
  2. [SOLVED] Userform pulling data but Next and Previous buttons do not function - Any thoughts?
    By JJFletcher in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 06-19-2016, 09:20 AM
  3. Replies: 3
    Last Post: 03-06-2014, 12:48 AM
  4. YTD Total Minus Previous Month
    By Emily2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2014, 01:44 PM
  5. Calculate varying % of Total depending on total sum.
    By mwest316 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-02-2013, 01:59 PM
  6. Total depends on previous total value
    By rohit1999 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-26-2013, 01:14 AM
  7. UDF for pulling value from previous worksheet
    By bltnwp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2010, 01:49 PM

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