# Pulling previous value to calculate total

1. ## 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. ## 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. ## 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. ## Re: Pulling previous value to calculate total

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

5. ## 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. ## Re: Pulling previous value to calculate total

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

7. ## 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. ## Re: Pulling previous value to calculate total

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

9. ## 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. ## Re: Pulling previous value to calculate total

Perfect! Thank you

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

#### 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