weight on a bar denomination calculator

[willRM]

I have created the following :
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl65, width: 320, colspan: 5"][TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Total[/TD]
[TD="class: xl67, width: 320, colspan: 5"]weight [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]45[/TD]
[TD="class: xl65, align: right"]25[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]220[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]

[/TD]
[/TR]
</tbody>[/TABLE]
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:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Total[/TD]
[TD="class: xl67, width: 64"]weight [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]45[/TD]
[TD="class: xl65, align: right"]25[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]220[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]100[/TD]
[TD="class: xl65, align: right"]25[/TD]
[TD="class: xl65, align: right"]-41[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0

[/TD]
[/TR]
</tbody>[/TABLE]
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)