sine function and figuring how to make block stacks

[Darth yoda]

Hi guys,
Need a bit of help in Excel 2007. I'm a machinist and often have to make stack of block to create angles. I can get the sin function to spit out a proper stack height but I want to give it a list of sizes to use with no two sizes being repeated.
The formula is : =SIN(E2*PI()/180)*E3

E2 is the angle wanted, such as 30 degrees and E3 is the length of the sine bar (usually 10,5, or 3 inches).
You enter the degrees and it spits out a number, in this case .500, then it is multiplied by the length of the sine bar (X3 in my case). The stack height is 1.500 inches. Easy enough...... right?

The hard part is 32 degrees and 3 inch bar. The correct answer is 1.5897 rounded to 4 places.
If I have a set of 36 blocks. Each block a different size. What I need to do is convert the right most number to zero using my .1007 block and remove it from the list so it cant be used again.
So now my Number is 1.4890 (1.5897-0.1007). The 9 is the next number so I use my .109 block for a remainder of 1.3800. The next blocks are .180, .200, and finally 1.000. each time the used block needs to be removed from the list.My guess would be a vlookup type funtion but have no idea how to remove blocks from the list.

Any help would be greatly appreciated.
Thanks in advance,
Darth

 

[RickXL]

Hi,

I don't know what you are trying to do.

Are you trying to see which angles you can make from your set of blocks? If so, 36 blocks means 2^36 different lengths which is 68719476736.

Also, you did not provide a complete list of block sizes.

Does something like this help?

Excel 2013
	A	B	C
1	Size	Use?	Result
2	0.1000		0
3	0.1001		0
4	0.1002		0
5	0.1003		0
6	0.1004		0
7	0.1005		0
8	0.1006		0
9	0.1007	1	0.1007
10	0.1008		0
11	0.1009		0
12	0.1000		0
13	0.1010		0
14	0.1020		0
15	0.1030		0
16	0.1040		0
17	0.1050		0
18	0.1060		0
19	0.1070		0
20	0.1080		0
21	0.1090	1	0.109
22	0.1100		0
23	0.1200		0
24	0.1300		0
25	0.1400		0
26	0.1500		0
27	0.1600		0
28	0.1700		0
29	0.1800	1	0.18
30	0.1900		0
31	0.2000	1	0.2
32	0.4000		0
33	0.8000		0
34	1.0000	1	1
35			1.5897
Sheet1

Cell Formulas
Range	Formula
C2	=B2*A2
C3	=B3*A3
C4	=B4*A4
C5	=B5*A5
C6	=B6*A6
C7	=B7*A7
C8	=B8*A8
C9	=B9*A9
C10	=B10*A10
C11	=B11*A11
C12	=B12*A12
C13	=B13*A13
C14	=B14*A14
C15	=B15*A15
C16	=B16*A16
C17	=B17*A17
C18	=B18*A18
C19	=B19*A19
C20	=B20*A20
C21	=B21*A21
C22	=B22*A22
C23	=B23*A23
C24	=B24*A24
C25	=B25*A25
C26	=B26*A26
C27	=B27*A27
C28	=B28*A28
C29	=B29*A29
C30	=B30*A30
C31	=B31*A31
C32	=B32*A32
C33	=B33*A33
C34	=B34*A34
C35	=SUM(C2:C34)

 

[Darth yoda]

That is close and would work. RickXL,thanks for the quick reply!
I cant attach a zip file or anything for that matter. I couldnt post a screenshot either.
The full list of sizes are: (starting in column E5 to E39)
0.0500
0.1001
0.1002
0.1003
0.1004
0.1005
0.1006
0.1007
0.1008
0.1009
0.1010
0.1020
0.1030
0.1040
0.1050
0.1060
0.1070
0.1080
0.1090
0.1100
0.1200
0.1300
0.1400
0.1500
0.1600
0.1700
0.1800
0.1900
0.2000
0.3000
0.4000
0.5000
1.0000
2.0000
4.0000

Two questions are asked,angle and sine bar length, in E2 and E3. In B9 and down the results should be shown.
I would like for it to be automatic after the questions are answered. Is it possible?
Thanks again!

 

[RickXL]

Hi, you say you have 36 blocks but you have only listed 35.

Which one is missing, please?

 

[shg]

0.1000

 

[RickXL]

Thanks for the confirmation shg, I thought it must be because:

1. It messes up the sequence without it and
2. I just found a 36 piece set described which is otherwise identical to the supplied list:

Block sizes included in each set:
36 pieces in wood case, consists of:
9 Blocks .1001″ thru .1009″ step of .0001″
9 Blocks .101″ thru .109″ step of .001″
9 Blocks .110″ thru .190″ step .01″
5 Blocks .100″ thru .500″ step of .1″
3 Blocks 1″, 2″, 4″
1 Block .050″
- See more at: 36 Piece Square Ceramic Gage Block Set

 

[shg]

Would you be comfortable using a user-defined function?

Row\Col	B​	C​	D​	E​	F​	G​
2​	Length​	Blocks​	Check​	Error​
3​	3.4169​	0.1000 0.1009 0.1060 0.1100 1.0000 2.0000	3.4169​	0.0000​		C2: =GageBlocks36(B2)
4​	7.7353​	0.1003 0.1050 0.1300 0.4000 1.0000 2.0000 4.0000	7.7353​	0.0000​		D2: =TotalHeight(C2)
5​	7.1810​	0.1010 0.1800 0.4000 0.5000 2.0000 4.0000	7.1810​	0.0000​		E2: =B2-D2
6​	1.3247​	0.1007 0.1040 0.1200 1.0000	1.3247​	0.0000​
7​	7.2208​	0.1008 0.1200 1.0000 2.0000 4.0000	7.2208​	0.0000​
8​	6.7909​	0.1009 0.1900 0.5000 2.0000 4.0000	6.7909​	0.0000​
9​	6.0779​	0.1009 0.1070 0.1700 0.2000 0.5000 1.0000 4.0000	6.0779​	0.0000​
10​	7.4549​	0.1000 0.1009 0.1040 0.1500 1.0000 2.0000 4.0000	7.4549​	0.0000​
11​	0.6981​	0.1001 0.1080 0.1900 0.3000	0.6981​	0.0000​
12​	6.4609​	0.1009 0.1600 0.2000 2.0000 4.0000	6.4609​	0.0000​
13​	5.2727​	0.1007 0.1020 0.1700 0.4000 0.5000 4.0000	5.2727​	0.0000​

 

[Darth yoda]

You are right. .1000 ( mine is .1000 and not .100, which is really the same thing) is missing from the list.

shg, dont know what a user designed function is. Your chart looks inviting. To use it you would enter the height in B3 and it would automatically generate the list?
Again, thanks for the help.

 

[RickXL]

@shg - That looks pretty neat! I did wonder how you knew the missing gauge size

I have a macro solution as well. You type in the new criteria into E2 and E3 and the list of gauges (sorry for the English spellings - but I am English!) gets the figure 1 written alongside. You could use this to highlight a gauge or in a SUMIF() to check the answer.

I currently have it working from a Worksheet Change event so when the new data is entered the answer appears automatically.

	5.877852523		5.8778
Angle:	36
Length:	10
	0.0500
	0.1001
	0.1002
	0.1003
	0.1004
	0.1005
	0.1006
	0.1007
	0.1008	1
	0.1009
	0.1010
	0.1020
	0.1030
	0.1040
	0.1050
	0.1060
	0.1070	1
	0.1080
	0.1090
	0.1100
	0.1200
	0.1300
	0.1400
	0.1500
	0.1600
	0.1700	1
	0.1800
	0.1900
	0.1000
	0.2000
	0.3000
	0.4000
	0.5000	1
	1.0000	1
	2.0000
	4.0000	1

<tbody>
</tbody>

 

[shg]

@ DY: The workbook is at https://app.box.com/s/0iqd1amnmppbzvtnj52s5dlrudag4518. You'll need to enable macros.

@ RXL: That actually may be a more convenient format than my UDF. I'm curious how our approaches compare -- can you post your code?