Excel Experts: Calculating The Volume

Training
1

ok, after an hour of trying I am asking for help- this is hopefully a problem other weight lifters have encountered:

So I am using Excel to record my sessions, and now, months later, I got the idea to calculate the volume but I don’t know how to make Excel actually calculate it.

This is an example row:

Date/Lat Pulldown/3x6x100

so 3 sets of 6 reps of 100 pounds so the volume would be 1800.

So 3x6x100 is in the wrong format for excel to calulate it.
I Find and replace x with * to make it 36100
then I concatenate(“=”,cell) where cell is the cell containing 36100
and I do indeed get =36100

but I can’t get Excel to actually calculate the result. Manually I need to go in there and hit “enter” at the end of the line.

arg I really am curious to see what the volumes are but I can’t go through it manually- too much.

I tried changing the format of the cell, I tried copy, paste special values

I can’t get it to work.

thanks!

and please no one make fun of me for using Excel! Whatever it takes to lift weights, right? For me, I like to see my progression etc.

2

the way i just read what you did you put the sets, reps, and weight all in 1 cell. make each value its own cell.

make a column for sets, column for reps, column for weight. in the column to the right type in “=‘set cell’+‘rep cell’+‘weight cell’”

like if it was on the second row and the first column was the exercise it would be:
=B2+C2+D2
it will auto calculate the formula. you can even copy and paste it as far down as you need for other rows of info.

hopefully that will help.

3

Thanks!

wow, I am so glad my post was posted- I thought maybe they didn’t think it belonged here it took so long to show!

BUT while I was waiting, I found the solution!!!

asooneyeonig, TY but that is sort of not what I want because lots of my sets look something like:

15x60, 12x70, 10x80, 6x90, 6x90

so I want all that in one cell because it is just more manageable that way.

Anyway, it took me maybe 2 hrs in all to find a solution to this so I will post it in case anyone else needs it.

So typical row would be:

Date Exercise Sets
Jan 1 Lat Pulldown 15x60, 12x70, 10x80, 6x90, 6x90

so those 5 sets are in one cell- cell c2 for example.

this is how I did it…

highlight column C
copy and paste to column D
find and replace x with * and , with +
highlight this column D after that is done
go to Formulas
go to Define Name
Name the column “sets”
Highlight column E
go to Formulas
go to Define Name
Nate the column “volume”
at the bottom of this dialog box there is a place that says “refers to”
at “refers to” enter =Evaluate(sets)
go back to the worksheet and enter in column E =volume and fill down.

That’s it!

this was the only place I found how to do it:

so the row I end up with would be like:

Date Exercise Sets Dummy Volume
Jan 1 Lat Pulldown 15x60, 12x70, 10x80, 6x90, 6x90 1560+ 1270+ 1080+ 690+ 6*90 3620

And then I can just delete the dummy column

Date Exercise Sets Dummy Volume
Jan 1 Lat Pulldown 15x60, 12x70, 10x80, 6x90, 6x90 3620

4

LOL.

5

wow, you are way overcomplicating things in excel. if you are using different weights for different sets then just make each row a different set. like this:

A : B : C : D : E
exercise : weight : set : rep : total
bench : 135 : 1 : 10 : (formula used) =B2C2D2
: 155 : 1 : 8 : (formula used) =B3C3D3
: 175 : 1 : 6 : (formula used) =B4C4D4
: : : : =sum(E2:E4)

this will auto populate the formulas without any changes and without having to add then delete anything later. it takes a minute flat to setup. you can event set up the first rows formula then copy and paste it down and it will update the row variables for you.

if you are going to use excel learn to use excel and make it as easy as possible. your way does work, but is extremely inefficient. and i am not saying this just to be mean. at work i am in charge of level I and level II desktop support at a fortune 500 company. it is also my job to train people or get proper training for people. excel is something i work with everyday.

microsoft has done a great job with leaving in old outdated functionality that is inefficient or just plain silly. they do this for those that are used to doing it a certain way and do not want to change. there are usually 3 ways to accomplish a task with any M$ program. not all of them are efficient or what would be called a best practice.

6

Thank you asooneyeonig…I did consider your method when I started- that is a very obvious way of doing it but not what I wanted. I understand your point but with your method, my 5 set lat pulldown routine will take up 5 rows. So a whole workout with say 5 exercises will take 25 rows. So it will be way less compact and I lose the ability to see things at a glance.

Part of the reason I am using Excel is to be able to sort wrt to lift type to see my change over time- so if I do a lat pulldown series each week, 2 months will only take 8 rows- with your method it will take 40 rows. I guess with your method you could do some sort of summary on another sheet but with my method I have solved the issue and I believe it is a more elegant solution for what I am wanting to do. I also want to be able copy my exercise summary into my journal here- and keeping the sets/reps/weights in one cell will make it easier to do that.

But thank you very much for your input, I appreciate it.