# sine function and figuring how to make block stacks

#### Darth yoda

##### New Member
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.
Darth

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
ABC
1SizeUse?Result
20.10000
30.10010
40.10020
50.10030
60.10040
70.10050
80.10060
90.100710.1007
100.10080
110.10090
120.10000
130.10100
140.10200
150.10300
160.10400
170.10500
180.10600
190.10700
200.10800
210.109010.109
220.11000
230.12000
240.13000
250.14000
260.15000
270.16000
280.17000
290.180010.18
300.19000
310.200010.2
320.40000
330.80000
341.000011
351.5897
Sheet1
Cell Formulas
RangeFormula
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)

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!

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

0.1000

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

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​

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.

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

Replies
0
Views
1K

1,203,205
Messages
6,054,140
Members
444,703
Latest member
pinkyar23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back