A useful skill that every strength athlete, coach, and trainer should possess is how to build calculators in Excel. This provides one with the ability to find their 1-rep max and easily program percentages based off of those 1-rep maxes.
There are multiple ways to calculate a 1-rep max, which include a variety of 1-rep max estimations. Everyone has their favorite way of finding a 1-RM, but for the sake of brevity we’re going to use one, and that’s the NSCA’s calculations from their book Essentials of Strength and Conditioning 4th Edition.
Before we can begin to dive into the logistics of building spreadsheets that automatically pull training percentages, we must first understand how to build a basic 1-rep max calculator (so we have a number to create training percentages from).
1. Setting Up Your Spreadsheet
There are multiple ways to set up your spreadsheet and you can be as creative as you’d like with the surrounding colors, designs, and logos. Personally, I use three calculators for finding an athlete’s max, which include a 3-RM (elite), 5-RM (intermediate), and 8-RM (beginner).
The reason I use these calculators is because most of my clients aren’t at a skill level to be testing a true 1-RM. For example, my younger athletes who play other sports won’t necessarily benefit from finding a true 1-RM deadlift (risk:reward ratio isn’t there in my eyes). Yet, that doesn’t mean you can’t use a 2-RM or 4-RM calculation, so keep in mind this is only my method for finding a client’s estimated 1-RM.
2. Plugging in the Calculations
This is the trickiest part, but also the easiest once you get the hang of it. If you have no experience with Excel, then you might have a bigger learning curve than someone who’s familiar with the program already.
Once you’ve decided on your 1-RM numbers, then now it’s time to plug them in. Below is an example of how you’re going to plug in formulas that correspond with the spreadsheet cells of your choosing.
- Start by selecting a cell that you’d want to write your final rep weight in, aka the weight you lifted (mine was B5 where I hit 275 for 3).
- Next, choose a cell you want your 1-RM equation to show up in (mine was C5 for an estimated 3-RM equation).
- From my example, you’d click on C5, which is where I want the final estimated 1-RM for 3-reps to display and I’d write “=B5*1.08” in the cell.
- “=” tells Excel that I want a product, or answer to my written equation.
- “B5” tells Excel to pull the number from the B5 cell into my written equation.
- “*” tells Excel to multiply B5 by what I choose.
- “1.08” (3-RM equation) is the number I’m telling Excel to multiple my B5 cell to for my final answer, which will show up automatically in C5.
Wrapping Up and Percentages
Once you’ve conquered calculating 1-RMs, then you can move onto programming training intensities. For percentages, you’d write similar formulas to the 1-RM calculation and manipulate intensities accordingly to your max.
For example, 80 percent would be .80 in Excel. You’d simply select the cell you’d want your training intensity to show up in and pull in the cell your 1-RM is represented in. If we used my example above, then we’d click our desired product cell, let’s say D5, and write something like “=C5*.80” if we wanted 80%.
We’re telling Excel to take C5 (our 1-RM cell) and to multiply it by .8 to give us our 80% training intensity.
Hopefully this article helped clear up some confusion around using Excel to calculate 1-rep maxes. It can be a useful tool when programming for yourself and others in a quick and easily understandable manner. Periodization made simple.
