How to set up an automatic hole sequence with excel?

3D Design Volume Model
1

Hello 

I'm looking to have a sequence of holes for example on a 1700 long flat with a hole on each side that remains 100 from the edge and in the middle of these two holes a number of holes not exceeding 250 mm so there in my example we fall well because 1700 - 200 = 1500 / 6 = 250  but if I have a flat that is larger for example 2640 - 200 = 2440 / 6 = 406.66 

So I will want that in my hole sequence it adds holes automatically so that my hole  center distance does not exceed 250mm.

I managed to tinker with a formula that is in B3 my length (2440) in B2 =(B3-200)/6  in B4 = SI(B2>250;( B3-200)/250+1; 6) to know the number of holes and to finish in B5 =(B3-200)/B4 which is my final distance between each hole. 

On excel it seems to work but I must have a problem with my first two fixed holes which are 100 from the edge. because on my piece arriving at the last hole I do not find the same center distance (either smaller or larger).  If you have another formula to propose to me will be welcome.

Moreover on inventor this formula "works" on the sketch so I take my example of my length 1700 I have my hole sequence I drill the 6 holes . And there I modify my length by 2440, on the sketch it adds many circles and modifies the axes but on my part it represents me only the first 6 holes with the new axis but not the others since at the time of my extrusion it does not exist. So how do you make the newcomers in the sequence break through automatically? 

I hope I have been clear in my request and thank you in advance.


test.png
2

It seems to me that linear repetition alone can answer your problem (without an equation) you can go to a reference, give it a minimum distance and spacing. 

http://help.solidworks.com/2015/french/WhatsNew/t_controlling_linear_patterns_with_reference_geometry.htm?id=eb7250f39612471b8ae13fc9f7f7bb51#Pg0

1 Like
3

Hello

I think you should write your formula in B4 as follows:

=SI(B2>250; ROUNDED. SUP((B3-200)/250)+1; 6)

In your example, you should have a value in B5 of 2240 / 10 = 224 and not 224.9 which comes from dividing by 9.96.

That probably explains why your last center distance is not good.

To display the missing holes, I don't know inventor so no solution to propose (unless you make holes in the void in advance but it's ugly).

 

@max, the question being on Inventor not sure if the help of solidworks is applicable

1 Like
4

Good evening, and thank you for your answers.

First of all, max59 this technique is not bad but I don't know if it works on inventor.

Chamade your formula will help me well for my last center distance now it's good but if I but at 1700 the my formula in B4 marks me wrong so it doesn't change on inventor :/ 

and how to make it work also below 1700 because with my formula as soon as I go below 1700 it is always based on 6 holes. For example my length is 1000-200 so 800/6 = 133.33 while its will have to go to 800/4 = 200.

 


test_2.png
1 Like
5

Hello, here is a formula that must work.

It's the same principle as for me when I calculate balustrade elements.

These elements vary in number but the maximum spacing must not exceed 110 mm.

in A1 your plate length

in B1 and C1 the 2 withdrawals on each side in this case 100 mm for you

and in D1 the maximum value not to exceed 250 mm

=(A1-(B1+C1))/ROUNDING. SUP((A1-(B1+C1))/D1; 0)

I did some tests in it works

4 Likes
6

I just tested with your example of 1000 and it works for me.

And I also work with Inventor

2 Likes
7

Hi franck51 thank you for your formula it gives me the center distance but suddenly I can no longer find the number of holes has you a solution to find 4 holes on my example of 1000-200 with center distance 200mm.

Because to put on inventor I need the final center distance and the number of holes by the way, is it what you do how to make the new holes add this hole by themselves? should we use drilling or

Sheet metal >cut? If you know the way I'm interested again, thank you.


test_inv.png
8

In the @Franck formula, the number of holes is given by ROUNDING. SUP((A1-(B1+C1))/D1;0. If you want to get this value, you have to put it in a specific box and recall this box in the division to get the center distance.

Moreover, indeed, there is no need to use an SI() function. This function allowed you to have a number of holes that cannot be less than 6 but that's apparently not what you want.

1 Like
9

A small correction, the formula gives the number of intervals. To get the number of holes (including initial drilling) you have to add +1 at the end.

In this case, copy only the formula (round.sup...) by adding 1 and do not modify the @Franck formula.

1 Like
10

Thank you franck and chamade rib formula I'm good. All that is left to do is to find out how to make it pierce automatically is the need for an expert on inventor.

 

Good evening to you:)

11

Good evening lolio60

It's very simple, you just have to create a network of your piercing function.

You do your first piercing then you make a network of this piercing of the number of piercings.

Since in the formula I gave you you you can extract the number of piercings there is no problem.

Good evening I hope I have helped you.

2 Likes
12

lolio60

Don't forget if you can to validate what you think is the best answer to your question.

And if you have any other questions, don't hesitate to ask.

Have a good day everyone.

13

Good evening

Sayer I found which network you are talking about I use the network in the sketch so it just modify in the sketch while you have to go to sheet metal and choose the network here. So impecable everything works perfectly, thank you all:D