weight on a bar denomination calculator

[willRM]

I have created the following :

	Total weight 45 25 15 10 5 220 4 1 1 0 0 100 <colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

I am trying to get the total to be split down into the different weights:

B3: =INT(A3/B2)
C3: =INT(($A3-SUMPRODUCT($B$2:B$2,$B3:B3))/C$2)

it only works on line 1. once expanded:

Total	weight
	45	25	15	10	5
220	4	1	1	0	0
100	25	-41	0	0	0

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

Obviously not correct please help

 

[FormR]

Hi, I think you just need the red addition in the quote below:

B3: =INT(A3/B$2)

 

[willRM]

that has actually worked. thank you very much!

 

[willRM]

i'm trying to make it a bit more complex. with putting weights on to a olympic bar you will only put the weight on if you can do one both sides. So if if the number of certain weighted plates used is not = 2 , then i want it to be 0.?

 

[FormR]

i'm trying to make it a bit more complex. with putting weights on to a olympic bar you will only put the weight on if you can do one both sides. So if if the number of certain weighted plates used is not = 2 , then i want it to be 0.?

Hi, not sure I follow, maybe it would help if you could you post some example data along with the expected results?

 

[FormR]

not sure I follow

Hi, the penny dropped - you could give this a try:

Excel 2013/2016
	A	B	C	D	E	F
2		45	25	15	10	5
3	220	4	0	2	0	2
4	100	2	0	0	0	2
Sheet1

Cell Formulas
Range	Formula
B3	=INT(INT(A3/B$2/2)*2)
C3	=INT(($A3-SUMPRODUCT($B$2:B$2,$B3:B3))/2/C$2)*2

 

[willRM]

you are an absolute legend. thank you . i assume its mathematical

 

[Dave Patton]

A slightly different solution.

Excel 2010
	A	B	C	D	E	F	G	H
6	Total	Weight Pairs
7		2 weights per
8	Weight	45	25	15	10	5	1	0.05
9	Weight * 2	90	50	30	20	10	2	1
10	220	2	0	1	0	1	0	0
11	100	1	0	0	0	1	0	0
12	235	2	1	0	0	0	2	1
13
1b

Cell Formulas
Range	Formula
B9	=B8*2
B10	=INT(A10/B$9)
C10	=INT(($A10-SUMPRODUCT($B10:B10,$B$9:B$9))/C$9)