Formula for Calculating Optimum Ethernet Switches

[JamieGEnt]

Hi,

I am trying to implement an IT system configuration tool using Excel formulas (without VBA).

We have 2 Ethernet switch parts available in our inventory: 6 Connections and 12 Connections.

We want to use Excel to give an indication of the optimum switches to use based on the number of connections required at each location.

For example; 54 connections required over 3 switch locations. We would use 1x 12 way and 1x 6 way to provide 18 connections at each location.

Any ideas how I would go about it in an efficient manner?

Thank you.

[Pepe Le Mokko]

Hi and welcome
as I suspect most of our members are not network savvy, could you please explain this in layman's term and eventually the mathematical logic?
Thank you
Also please post a sample sheet ( no pics) with some data and expected results

[JamieGEnt]

An analogy:

We sell apples in pack sizes of 6 and 12.

We have customers that prefer these in 6 and others that prefer these in 12.

Our customers will order a specific number of apples and we have to supply them in pack sizes to give them at least this amount.

A customer for example who wants 16 apples and prefers packs of 12 will get 1x 12 pack and 1x 6 pack.

[FlameRetired]

Using the analogy see if this helps.

	A	B	C	D
1	Ordered	Packs of 12	Packs of 6
2	16	1	1	In B2 =QUOTIENT(A2,12)
3	20	1	2	In c2 =CEILING((A2-B2*12)/6,1)
4	48	4	0
5	11	0	2
6	9	0	2
7	12	1	0
8	38	3	1
9	44	3	2
10	18	1	1
11	43	3	2
12	10	0	2