Forum Discussion
Excel weightlifting math
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
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.
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.
so I didn't see this until now and as I read through was actually thinking the same thing as ErikMakela . Granted the original post was very lacking and was completely AWOL for the discussion. However, for fun I thought I would add another option to this thread using Lambda:
=LET(t,target,w,weights,b,45,IF(t-b<0,"Target Weight must be greater than bar weight ("&b&")", DROP(REDUCE(t-b,w,LAMBDA(p,q, LET(remaining,INDEX(TAKE(p,-1,-1),1,1), v,2*QUOTIENT(remaining,2*q), HSTACK(p,VSTACK(v,remaining-v*q))))) ,-1,1)))
