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.

Try this ...

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

Enter with Ctrl+Shift+Enter.

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.

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?

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

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

Perfect! Thank you