Sep 22 2021 12:28 PM
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?
Sep 23 2021 04:36 PM
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!
Sep 24 2021 11:25 AM
Sep 24 2021 11:41 AM
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.
Sep 24 2021 12:10 PM
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
thx for your time
Sep 24 2021 01:39 PM
@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.
Sep 24 2021 01:42 PM
@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.
Sep 26 2021 08:47 AM
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.
Sep 26 2021 09:52 AM
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