Forum Discussion
Excel weightlifting math
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 A3All 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.
Fair enough, but in our defense, we were focusing on the math aspect of the question, not the formalities of weightlifting. Does all weightlifting involve a bar with balanced sets of weights? My experience with lifting weights involves such things as lifting heavy suitcases, which sometimes involves a pair of suitcases, but often is extremely unbalanced.
But at any rate, as you noted, the original question may have left off some important dimensions to the questions, assuming (as I've noted often happens) that all people are fully familiar with what in fact is a niche interest.
