Forum Discussion
Excel weightlifting math
I am trying to make a sheet for knowing how to breakdown my weight into the plates (45, 25, 10, etc) for example;
155 LB 45. 25. 10. 5. 2.5.
2. 2.
What would the cell formula be?
11 Replies
I played around with this a bit more this morning and came up with a new solution. The formula for the 45 lb weights is one of a kind, but thereafter all the others are the same. Here's what the work area looks like.
The first formula, in cell C2, (to calculate how many 45 lb weights might be needed) is this:
=(A2-MOD(A2,C1))/C1 where A2 is the target weight, C1 is 45.
MOD(A2,C1) yields up the remainder, if any, after dividing the target weight by, in this case 45
The larger formula subtracts that remainder from the target weight and divides it by, in this case, 45, which is the number of 45 lb weights in the final result.
The rest of the formulas are this (with relative and absolute references making it work in each column from column D through G. NOTE: This formula uses the LET function, which requires the most recent version of Excel in order to work.
=LET(mssng,
MAX($A$2-SUMPRODUCT($C$1:C1,$C$2:C2),0),
(mssng-MOD(mssng,D1))/D1
)
The first step is to calculate the whatever value has not been satisfied by the weight allocations in the columns to the left. This is done by subtracting the SUMPRODUCT result of the rows displaying weight and number of weights and assigning that value to the variable mssng
Then the formula that does the work resembles exactly, with the substitution of the variable mssng, the formula described above. It takes whatever is remaining of the target weight, calculates the remainder using the new pound variable, subtracts that from the value of mssng, and figures out how many of that size are needed.
You are absolutely right, "help" not only help those who have been helped, but also those who help :)).
I don't know if this can help, but here are two other formulas that could lead to the same goal.
=IF(A3>=B1,ROUNDDOWN(A3/B1,0),0)
=INT($A$3/B$2)
Regards,
NikolinoDE
All previous sheets WILL NOT lead to the ideal goal of the proposed question. Within weight lifting you are looking for symmetry on each side, thereby any weight amount cannot be odd because they must be evenly distributed on each side. Therefore, it must be rounded to a multiple of two so that the rest of the sheet can calculate down further in weight.
Both you and mathetes forgot to include the fact that the bar is included in a lift and accounts for 45lb of weight. Which is a mistake by the question poster. For example, if I input a weight of 100 then I get 2x45lb and 1x10lb. The ideal distribution for 100lb would be 2x25lb, 2x2.5lb since the bar itself weighs 45lb and there is 55lb left to be split.
This is a fairly simple fix by utilizing the floor function (to round down) and a separate reference value that will take the total weight and subtract it by 45.
Referencing your most recent sheet these are the fixes needed:
B3: =FLOOR(INT($A$6/B$2),2)Where A6 = Total Weight(A3) - 45
C3-F3: [Insert FLOOR(_,2) into each function]
C3-F3: Reference A6 instead of A3
- What is the logic of the distribution? You could also use 3 * 45lb + 2 * 10lb,
or instead of a 25kg plate 10 * 2.5.
Different combinations possible.
Please be specific, if possible also a sample file (without sensitive data).
Thanks very much,
NikolinoDEYou're certainly correct. But I assumed in my more simplistic way that you'd want as few weights on a bar as possible, which means take the heaviest first, see how much of the desired total can be achieved with them, then next heaviest on down.
Achieving 25 lb with 10 x 2.5 would not be practical, even though theoretically the same weight.
Your acceptance as well as your presentation is very correct and well structured.
The problem is the question.
It is an open question that allows a lot of guesswork.
I would have followed the same logically as you, but mostly such questions are not the obvious.
Since Sbermab himself does not know exactly how he would like to solve his problem,
otherwise he would be, I think, more precise.
I only asked to save time as much as possible.
All good 🙂 ... I wish you a pleasant evening and have a nice weekend.
thx for your time 🙂
An interesting problem. I'm surprised nobody else has responded.
My solution probably isn't the most elegant, but it was fun playing around with it. I've even added a secret cell that will appear if you enter a number that doesn't work.
There actually are two formulas here. It probably would be possible (in fact, I'm quite sure it would be) to make it only one formula, but I thought it would be fun to have the intervening step visible. I did make that intervening step visible in only faint numbers, so it looks like this, set for 155 lb
And here it is for 170
Have fun!
