Excel weightlifting math

[Sbermab]

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?

[#2]

@Sbermab 

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!

[#3]

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,
NikolinoDE

[#4]

@NikolinoDE 

You'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.

[#5]

@mathetes 

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 :)

[#6]

@NikolinoDE : You wrote ... 

The problem is the question.

It is an open question that allows a lot of guesswork.

.... but mostly such questions are not the obvious.

How very true. I find myself going back, as you did here, more times than not, because the questions are poorly formed, allow for far too many interpretations or, worse, are far too vague to even come up with an interpretation. Have a good weekend.

[#7]

@NikolinoDE : You wrote ... 

The problem is the question.

It is an open question that allows a lot of guesswork.

.... but mostly such questions are not the obvious.

How very true. I find myself going back, as you did here, more times than not, because the questions are poorly formed, allow for far too many interpretations or, worse, are far too vague to even understand.

This one, I left alone for quite a while, and then finally tackled partly just to see how I could (if I could) resolve it. And it ended up being fun and more straight forward than I expected. I'd never used, to my memory, the MOD function, but it was there in the back of my mind and turned out to be very helpful.

[#8]

@Sbermab 

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.

[#9]

@mathetes 

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

[#10]

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

[#11]

@ErikMakela 

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.

[#12]

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)))