Full downloadable calculator at bottom of page!
Introduction
In a previous article, entitled "Determination of Repetition Maximums" [1], I demonstrated graphical and mathematical methods to determine your repetition maximums (RMs). If you've read that article, then you already know that determining your RMs by hand, for every exercise, can be a long and tedious job, indeed. So why not just have your personal computer do all work for you?
Herein, I demonstrate steps you can take to create a simple RM calculator using Microsoft ExcelTM. Once you've created your calculator, you'll be able to determine any of your RMs for any exercise in a fraction of the time it would otherwise take.
Of course, the following sections are written with the assumption that you know your way around an ExcelTM spreadsheet and are familiar with the discussion and formulae given in [1]. Even so, I'll try to keep things as straightforward as possible.
 |
The Mathematics Of Bodybuilding For The One Rep Max! The mathematically-derived coefficient table has been a tremendous benefit to me and thousands of other power bodybuilders for determining a true single-rep maximum in a selected exercise. Find out what it is and how it can help you! [ Click here to learn more. ] |
Review Of The Underlying Concepts
As you'll recall, a large portion of [1] was dedicated to creating a graph which illustrates the relationship between the weights you can lift and the number of repetitions you can perform with those weights. Following is one example of such a graph.
The sloping line is a best-fit line which, to a good approximation, relates your weights to the number of reps you can perform. A general equation for the best-fit line is
(1)
where W is a particular weight that you lift and R is the number of repetitions that you can perform with that weight. Also, m is the slope of the line and b is an intercept where the best-fit line crosses the vertical Weight axis.
When you do things by hand, the intercept b has to be determined by looking at where the best-fit line crosses the Weight axis, and the slope m must be computed by using the equation:
(2)
where the values {W1, R1} and {W2, R2} are respective weight and rep values of two points that you select on the best-fit line. Carrying out this procedure for each and every exercise in your training regimen would certainly take some time. So, now let's find out how we can get Microsoft ExcelTM to do all the dirty work for us.
Setting Up A Simple RM Calculator
We can begin by opening a new spreadsheet and using the File, Save As menu command to save your new spreadsheet. It's a good idea to save your work often so that you don't lose anything if there's a power failure, or some other unforeseen problem. As you can see below, I've saved my spreadsheet with the name, "Repetition Maximums Calculator."
For those users that haven't used spreadsheets in a while, notice that the spreadsheet is arranged into columns and rows. Columns are identified by letters, and rows are identified by numbers. The box where a column intersects a row is called a "cell." Each cell in the spreadsheet is identified by the column and row that make up the cell. For example, in the spreadsheet above, cell A1 is currently selected.
You can select cells by clicking on them with the mouse, and you can select groups of cells by dragging across them with the mouse. If you're a little unsure of your skills, you may want to spend some time selecting different cells, as well as groups of cells, to familiarize yourself with this feature. You'll be using quite a few cells to create your RM calculator.
It's always a good idea to give your spreadsheet a title so that you can tell what it does when you open it later on. Let's go ahead and enter the text "Repetition Maximum Calculator" into cell A1. Since cell A1 is already selected, you can simply type the text and then press Enter.
Let's add labels that identify the data that you'll enter into the calculator later. You can begin by selecting cell A4 and then entering the text "First nRM." Note that we're using "nRM" to represent one of your known RMs. The "n" simply represents a number of repetitions that you can perform.
Select cell A5 and enter the text "Second nRM." Recall that a RM is defined as a particular weight that you can lift a certain number of times. To place this information into the spreadsheet, select cell C3 and enter "Weight," and then select cell D3 and enter "nReps."
Once you've entered the known RM labels, you're ready to enter labels for the Slope m and the Intercept b. The calculator will use these values to determine your unknown RMs. Select cell A7 and enter the text "Slope m (lbs/rep)," and then select cell A8 and enter the text "Intercept b (lbs)." Notice that we've included the units lbs and reps for the slope and the intercept.
This helps us to keep track of the types of values that are being used by the calculator. It's useful to keep in mind that the calculator will be actually using Eqs. (1) and (2) to determine your RMs. Let's finish up the labels by entering "Desired #Reps R:" into cell F4 and then entering "Weight to Use W:" into cell F5. Your spreadsheet should now look like the following spreadsheet.
The following list summarizes the cells used and their content.
Now that you've entered all of the data labels into the spreadsheet, it's time to enter the equations that enable the calculator to function. Let's begin with the equations for the slope and the intercept of the best-fit line, given by Eq. (1). To enter the equation for the slope of the line, select cell C7 and type "=(C4-C5)/(D4-D5)."
Be sure to include the equal sign at the beginning of the equation; that's what tells ExcelTM that you're entering an equation. Also, notice that the cells become highlighted in the spreadsheet as you enter their names in the equation, as shown below.
When you press Enter, you'll notice that cell A7 no longer displays the equation you entered, but displays "#DIV/0!" instead. That's not a problem; it's simply a warning that the equation is dividing by zero. When you later enter your known RMs into the spreadsheet, the #DIV/0! will be replaced by a calculated value.
Continuing, let's enter the equation for the intercept by selecting cell C8 and typing "=C4-C7*D4." When you press Enter, "#DIV/0!" is displayed in cell, like before. This happens simply because you haven't yet entered any data into the cells used by the equation in cell C8.
Let's finish up by selecting cell H5 and entering the equation "=C8+C7*H4." When you finish typing the equation in cell H5, your spreadsheet ought to look like the one shown below. When you press Enter, "#DIV/0!" is displayed in cell H5.
You've finished entering all the equations necessary for the RM calculator to determine your RMs. The list below summarizes the equations that you've entered into the spreadsheet. Also, now is a good time to save your spreadsheet, again.
With the equations now entered into the spreadsheet, you're ready to test your new RM calculator! Let's suppose that you know you can bench press 160 lbs for 8 reps and 140 lbs for 13 reps, but you want to know how much weight you can bench press for 10 reps.
As you may recall, we used this example in [1] and found that your 10RM for the Bench Press is about 152 lbs. Let's see if your new RM calculator produces the same result.
Your "First nRM" is an 8RM of 160 lbs. So, let's select cell C4 and enter 160 and then select cell D4 and enter 8. Continuing, since your "Second nRM" is a 13RM of 140 lbs, let's enter 140 into cell C5 and 13 into cell D5. When you do this, notice that the Slope m becomes -4 lbs/rep, and the Intercept b becomes 192 lbs.
These values are in agreement with those found in [1] using graphical methods. Now let's set R = 10 by entering 10 into cell H4. If all has gone as planned, "152" should now be displayed in cell H5, as shown below. This implies that your 10RM weight for the Bench Press is about 152 lbs.
The value displayed in cell H5, 152 lbs, is in agreement with the manual analyses performed in [1]. Therefore, our new RM calculator is working correctly!
Let's perform another test. Suppose that you now want to know what your 5RM weight is for the Bench Press. To find out, select cell H4 again and enter 5. When you press Enter, "172" is immediately displayed in cell H5, as shown below. Thus, the RM calculator is determining that your 5RM weight for the Bench Press is about 172 lbs.
As you can see, simply changing the number in cell H4 is much easier than performing an entirely new analysis every time you want to know a new RM value. Moreover, whenever you want to find RMs for a different exercise, you need only enter the weights and corresponding numbers of reps into the cells C4 - D5 and then enter a desired number of reps in cell H4. The beauty of the RM calculator is that it takes the tedium out of determining your RMs for several different exercises.
Another nice thing about setting up a RM calculator is that you can use fonts, borders, and shading to customize its appearance in any way you want. One way to format the RM calculator is as follows.
Expressing Weights As Percentages Of 1RM
Many printed and online training programs express the weights that you use as fractions or percentages of your 1RM weight. Because of this, it is extremely useful to set up your RM calculator so that it, too, expresses your weights as fractions and percentages of your 1RM weight.
It's important to note that when you do this, the values produced by the calculator are based on your known RMs that you entered into cells C4 - D5. Thus, the fractions and percentages that your RM calculator produces are personalized values that aren't likely to agree with the generalized values found online or in books.
Let's set up the calculator to express weights as fractions and percentages of your 1RM weight. You can begin by selecting cell F7, typing "Fraction of 1RM:" and pressing Enter. Doing this should automatically select cell F8. Enter the text "Percent of 1RM:" into cell F8.
Now let's enter the equations that enable the calculator to convert your weights into fractions and percentages of your 1RM. Select cell H7 and enter "=H5/(C8+C7)." As mentioned above, be sure to include the equal sign; it tells ExcelTM that the cell contains an equation.
When you press Enter, cell H7 should immediately display the number 0.914894. To convert this number into a percentage, select cell H8 and enter "=H7*100." With the exception of the formatting that I've added, your spreadsheet should now appear as follows.
Thus, in this example, 172 lbs is about 91% of your 1RM weight for the Bench Press. Remember, though; this percentage is based on the RMs that you entered into cells C4 - D5. As a consequence, the percentage in cell H8 is a personalized value that is unlikely to agree with the values found online or in books. The following list summarizes the information required for the RM calculator to express your weights as percentages of your 1RM.
Your RM calculator now only needs one more feature that's missing! Recall that just as you can express weights as percentages of your 1RM, it's also useful to express percentages of your 1RM as weight values. Let's include this feature in the RM calculator.
You can begin by entering the text "Enter a Fraction of 1RM:" into cell E10. Next, let's enter the text "Equivalent Weight (lbs):" into cell E11. You can finish up, now, by selecting cell H11 and entering the equation "=H10*(C8+C7)."
Did you notice that when you pressed Enter, "0" appeared in cell H11? That implies that the equation in cell H11 is working, but we need to enter a number into cell H10. Let's suppose that you want to work with 85% of your 1RM weight for the Bench Press. How much weight should you use?
Let's find out by entering the number "0.85" into cell H10. When you press Enter, "159.8" is displayed in cell H11, as shown below. Thus, in this example, if you want to work with 85% of your 1RM for the Bench Press, you should pile about 160 lbs onto the bar.
Again, it must be stressed that these percentage and weight values are based on the RMs that you entered into cells C4 - D5. The weight value in cell H11 is a personalized value that won't agree with the predictions of many of the RM calculators found in books and online. The following list summarizes the information required for the RM calculator to convert fractions of your 1RM to weight values.
Conclusions And Discussion
Many fitness programs require you to determine your RM weights for each exercise that you plan on using in your training. In addition, many of these fitness programs express the weights you use as fractions of your 1RM weight for each exercise. This makes having some type of method for determining your RMs essential.
Unfortunately, performing the calculations by hand is so time-consuming that most people would likely turn to one or more of the many Rep-Max calculators posted online or in books. As pointed out in [1], however, using these Rep-Max calculators may be easy and take very little time, but a drawback is that they generally are based on the performance of many different lifters, and thus aren't terribly accurate on an individual basis.
| Check out this related posting from our forums! |  |
 |
How To Determine Max Weight That I Can Lift. What is the best way for me to determine the max weight that I can lift? [Read More!] |
|
|
||
The techniques shown herein solve this problem by enabling you to take advantage of the accuracy the calculations have to offer, without spending a whole lot of time in the process. The amount of time you'll save by using your RM calculator to determine your RMs will more than make up for the time spent setting up the calculator. Moreover, once you've created your calculator, you can transfer it to your PDA.
With your RM calculator stored on your PDA, you'll be able to quickly and easily determine your own RMs, or those of your clients, for any exercise, anywhere, at any time!
1 Rep Max Calculator
Figure out what fraction of your 1RM you're using.
Click here to download the completed calculator!