Sorting and Grouping Problem

Grouping and sorting question.
I would like to sort data from cells into zones such as <30, 31-39, 40-49, 50-59, 60-69, 70-79, 80-89, 90-95, 96-100 and >100. But those zones are made up of a percentage of total volume

Here is an example of what I am trying to do. Let’s say I have a client I am doing personal training for and in the gym I want to record their number of repetitions, number of sets, weight lifted per rep, total weight lifted per set (reps x sets x weight).

For instance a client can lift 100kg for 1 rep (1RM) so during a training session they may lift
1 set of 10 reps with 60kg on bench press = 1 x 10 x 60 = 600kg
1 set of 10 reps with 70kg on bench press =1 x 10 x 70 = 700kg
1 set of 8 reps with 80kg on bench press =1 x 8 x 80 = 640kg

So for this 3 set exercise the total reps are 28, total weight lifted is 1940kg. However I want to know what percentage of the exercises total volume came from what intensity zone as dictated by the 1 rep maximum. So in the above example the first set has an intensity of 60% of 1RM (60kg/100kg*100), set two has an intensity of 70% and set three has an intensity of 80%. But I need to know how much volume of the overall exercise is contained within each set. In this case the first set has 30.9% of the total volume, set two has 36.1%, and set three has 33% of the total volume.

All of this is easy to calculate and I have manually put in the formulas to come up with the required information. However, if the 1RM changes or the number of sets changes or the number of repetitions changes I have to manually go through and change all of the volumes at specific intensities. Everything else on my spreadsheet is set to change automatically, and having to go through and manually put in formulas is so time consuming.

So my question is, how can I create a formula for the percentage of 1RM intensity zones and volume at that particular intensity?

Here is a more detailed example, note that the % of Set at Intensity Zones of 1RM is calculated by dividing set total by exercise total and multiplying it by 100 ‘=E3/F3*100’ or (Set total/Exercise total X 100). In some cases when an intensity zone had more than one data cell I manually added the set totals together before dividing them by the exercise total and multiplying by 100 here is an example of that, E# is the set totals and F3 is the exercise total ‘=(E5+E8+E9+E10+E11+E12+E13+E14+E15+E16+E17)/F3*100’

I have tried to use the count if function but becuase the zones are made up of a percentage of total exercise volume I have not been successful, any help would be greatly appreciated.

Regards
Ocris

Ho Ocris,

If you'd designate an absolute reference for your: 1RM, Sets and Reps - such as $R$3,$S$3 and $T$3 then you could just change those three fields and all of your formulas could remain as they were!!!

Hi Ocris,

Is your sheet set to automatically calculate? (Tools-Options>>>Calculation-Automatic)

Here's what I mean:

OcrisX.xlsx

You're welcome!