For Excel Formula Experts

3D Design Volume Model
1

Hello, today I have a question about excel formulas

 

I have this problem to solve:

 

I have to create an assembly with boxes, the spacing of these boxes must be equal, or else it must be in two parts.

There can (and is even very likely) therefore there be two spacing values between the boxes.

These two spacing values should fall right, not a comma. (That's why there are 2 spacings).

They are each between a MINI and a MAXI predefined in the excel table.

 

The total length is known in advance, it is always right.

The length of a box is known in advance, it is the same for all boxes (on one side or the other).

The number of boxes is defined by the user according to a list from 0 to 40 (for example).

In the image, the 2* spacing (two stars) is the result of the two spacings: it must be the same as the 2 spacing or the 1 spacing.

 

I am looking to perform these calculations on an excel sheet in order to create/modify this assembly on the fly. Thanks to the file I gave in this linked post: http://www.lynkoa.com/forum/3d/piloter-les-cotes-d-une-piece-dans-un-tableau-d-assemblage

 

Thanks in advance;)

 

http://img11.hostingpics.net/pics/341060Calcul.jpg

 

 

2

Hello

I have trouble understanding the spacings and their numbers.

How is the spacing number 1 and the number of 2 defined?

Could you tell me more about the 2* (why call it that if it's the same as the 2 or 1 spacing)?

 

A little lost at the time...

1 Like
3

It's true that it's not easy.

 

The number of spacing is actually related to the number of boxes:

 

The spacing is between two values (Mini and Max)

 

On the other hand, the number of boxes is not limited

 

So the number of spacing = Total length - (number box*n) = Remaining space

 

Remaining space is used by the spacings: Spacing * x (between minimum and maximum)

 

And where I get stuck is that there are two different ones (but it can also be equal...)

 

If you have any other questions, I'm here. I'll add + info as I find new things.

 

Thank you

4

I think the question is how to create equal spacing via

a total length x = n'box + ( n'box + 1)

n'box+1 = the number of spacing that must be between x mm and x mm

@+ 

1 Like
5

What I'm wondering is how do we define the number of spacings 1 and the number of spacings 2.

 

 

But, for example, we can calculate the maximum number of spacings 1 and then have 1 single spacing 2.

Is that good?

 

6

Hello

 

I agree with Yves in the non-understanding of the 2* value!

Are there 3 spacings (1, 2 and 2*) or 2?

Another question, how to determine the two spacings in automatic? I'll have to define a minimum of them or there's something that escapes me.

 

@+

 

1 Like
7

Hello

 

Thank you for your answers:

 

To the question are there 2 or 3 spacings: Ideally (if it's possible to do so) there are only 2. Otherwise I would say 3.

 

Yes, we could for example define the spacing 1 (we will use the MINI value) to then be able to have the 2nd.

 

I think you have to try with values to better understand the thing.

I also think that I have to put the formulas by hand before trying to measure them in excel.

 

The spacings (1 and 2) are not necessarily equal but ideally they are.

8

Re

 

Ok clearer for me, but remains a problem anyway how much space 1 compared to space 2?

It is easy to calculate the average spacing, to give the minimum value to spacing 1,  but then there are 3 unknowns (spacing2, spacing 1 and spacing2)

 

@+

 

1 Like
9

Re

 

I'm telling myself that the simplest solution is to make 1 space 2 

 

I'm not sure it works for all dimensions.

 

I don't have time today to do the DIM tests. 

 

The whole thing is that the 2 spacings fall just like this:

 

210mm / 220mm...

 

Thanks again

10

hoping that this helps you


classeur1.xlsx
1 Like
11

Hello

 

I think it would be easier to start with real values to get a correct answer.

 

I just did some tests and it's not necessarily too complicated, the problem is just to have reference values to be sure.

 

If I understood correctly, you want to know the intervals between your boxes, knowing that these values are integer, between a maximum and a mini, and that you can have a maximum of two different intervals.

 

The problem is that you can have several solutions.

 

Waiting for your values.

Here is a file for testing

 

Kind regards

 


essais_1.xlsx
1 Like
12

Starting from the fact that we only know the total length, the length of a box and the minimum/maximum values of the spacings, here is what it would look like:


classeur2.xlsx
2 Likes
13

I see that my subject is successful: thank you!

 

In fact, the best would be to have a list of proposals that the user could choose afterwards (could choose = in my excel sheet that will generate my assembly).

 

Like what:

 

For a total length of 1000mm

With 6 x 80mm boxes

 

6*80 = 480

1000-480 = 520 spacing

There are 5 spacing (6 boxes -1).

 

Minimum Spacing: 100

Maximum spacing: 150

 

 

Equal spacing on both sides = 104mm

 

--

 

Serves 4 and 1

For 3 and 2

For 2 and 3 (inverse so the same)

For 1 and 4 (inverse so the same)

 

---

 

For 4 and 1 =

 

Spacing 1:             /

Spacing 2:             /

 

For 3 and 2 = 

 

Spacing 1:             /

Spacing 2:             /

 

---

 

Notice that there is a list of possibilities such as for example for 12 spacings:

 

12 equals

11 + 1

10 + 2

9 + 3

8 + 4....

 

Thank you all once again 

Good evening;)

 

1 Like
14

Hello

 

I just made a file that apparently works.

 

Depending on the input data, your solutions appear.

 

I'm not sure if it works 100% but the tests I did worked.

 

Keep me informed of the results.

 

Kind regards


essais_1.xlsx
15

Hello

The solution proposed by Franck51 seems to work, for my part I prefer to use the excel solver. The problem is that with an identical number of boxes and spaces we find different space values

 

Franck51

7 boxes width 80

2 spaces width 115

4 spaces width 116

 

Me

7 boxes width 80

2 spaces width 131

4 spaces width 108

 

I use the following solver constraints: (I don't know if they are saved with the excel sheet)

Target to be set$F$15 value = 1

Variable Cell $F$4:$G$5

$B$15=$B$12

$F$4=integer

$F$4>=1

$F$5<=$F$7

$F$5=integer

$F$5>=$F$6

$G$4=integer

$G$4>=1

$G$5<=$G$7

$G$5=integer

$G$5>=$G$6


boite.xlsx
1 Like
16

Hello y.pacquelet

 

The difference actually comes simply from the calculation system.

In fact, to find out the spaces, I divide my total space by a value (= number of boxes - 1) and I use this value as a reference to find the second one.

This allows you to have similar values as you have seen.

 

Solution N°1

7 boxes width 80

2 spaces width 115

4 spaces width 116

 

Or

Solution N°2

7 boxes width 80

 4 spaces width 115

 4 spaces width 117

 

Or

Solution N°3

7 boxes width 80

 5 spaces width 115

 1 spaces width 119

 

Or your solution

 

Depending on the desired result, there are certainly as many calculation systems.

17

I have just modified my Excel file to include a number of automatic gearboxes calculated in relation to the length and the maximum and minimum space.


essais_1.xlsx