Excel weightlifting math

%3CLINGO-SUB%20id%3D%22lingo-sub-2775837%22%20slang%3D%22en-US%22%3EExcel%20weightlifting%20math%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2775837%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20make%20a%20sheet%20for%20knowing%20how%20to%20breakdown%20my%20weight%20into%20the%20plates%20(45%2C%2025%2C%2010%2C%20etc)%20for%20example%3B%3C%2FP%3E%3CP%3E155%20LB%20%26nbsp%3B45.%20%26nbsp%3B%2025.%20%26nbsp%3B%20%26nbsp%3B10.%20%26nbsp%3B%20%26nbsp%3B5.%20%26nbsp%3B%202.5.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202.%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202.%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20would%20the%20cell%20formula%20be%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2775837%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2780287%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20weightlifting%20math%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2780287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163651%22%20target%3D%22_blank%22%3E%40Sbermab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20interesting%20problem.%20I'm%20surprised%20nobody%20else%20has%20responded.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20solution%20probably%20isn't%20the%20most%20elegant%2C%20but%20it%20was%20fun%20playing%20around%20with%20it.%20I've%20even%20added%20a%20secret%20cell%20that%20will%20appear%20if%20you%20enter%20a%20number%20that%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20actually%20are%20two%20formulas%20here.%20It%20probably%20would%20be%20possible%20(in%20fact%2C%20I'm%20quite%20sure%20it%20would%20be)%20to%20make%20it%20only%20one%20formula%2C%20but%20I%20thought%20it%20would%20be%20fun%20to%20have%20the%20intervening%20step%20visible.%20I%20did%20make%20that%20intervening%20step%20visible%20in%20only%20faint%20numbers%2C%20so%20it%20looks%20like%20this%2C%20set%20for%20155%20lb%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1632440032822.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312544i749EE5C43232C7F2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1632440032822.png%22%20alt%3D%22mathetes_0-1632440032822.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20here%20it%20is%20for%20170%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1632440100182.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312545i9FE8B9A3D901BC1A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1632440100182.png%22%20alt%3D%22mathetes_1-1632440100182.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHave%20fun!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783203%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20weightlifting%20math%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20certainly%20correct.%20But%20I%20assumed%20in%20my%20more%20simplistic%20way%20that%20you'd%20want%20as%20few%20weights%20on%20a%20bar%20as%20possible%2C%20which%20means%20take%20the%20heaviest%20first%2C%20see%20how%20much%20of%20the%20desired%20total%20can%20be%20achieved%20with%20them%2C%20then%20next%20heaviest%20on%20down.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAchieving%2025%20lb%20with%2010%20x%202.5%20would%20not%20be%20practical%2C%20even%20though%20theoretically%20the%20same%20weight.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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?

8 Replies

@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

mathetes_0-1632440032822.png

 

And here it is for 170

mathetes_1-1632440100182.png

Have fun!

 

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

@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.

@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

 

 

@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.

@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.

@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.

mathetes_0-1632670399254.png

 

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.

 

 

@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