Calculate certain combinations from various lists

[anao_]

Hi,

I am trying to write a formula (without Macros, queries, or VBA) to create all the combinations from a list, given certain conditions.

I have several plants running several production lines, and if a production line breaks it has a cost as described below:

[TABLE="width: 551"]
<tbody>[TR]
[TD]Factory[/TD]
[TD]Failure of the first line[/TD]
[TD]Failure of the 2nd line[/TD]
[TD]Failure of the 3rd line[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10.000[/TD]
[TD="align: right"]20.000[/TD]
[TD="align: right"]n.a.[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12.000[/TD]
[TD="align: right"]1.200.000[/TD]
[TD="align: right"]n.a.[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]13.000[/TD]
[TD="align: right"]1.300.000[/TD]
[TD="align: right"]n.a.[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]19.000[/TD]
[TD="align: right"]95.000[/TD]
[TD="align: right"]1.900.000[/TD]
[/TR]
</tbody>[/TABLE]

As you can see above, the order in wich each line breaks makes a difference in the cost (i.e. the same line will have a different impact in production if it is the first, second or third line to break in that factory).

I am trying to come up with a formula that will give me authomatically all the potential combinations from my list, given a certain number of failures (I will then average those values to understand the expeced cost of failure of each number of failures).

For example, the formula should give me for 1 failure the following combinations: 10.000, or 12.000, or 13.000 or 19.000.
For 2 failures these would be the combinations:
10.000+20.000, 10+12.000, 10.000+13.000, 10.000+19.000,
12.000 + 1.200.000, 12.000 + 13.000, 12.000 + 19.000
13.000 + 1.300.000, 13.000 + 19.000
19.000 + 95.000

The trick here is the combinations always have to follow the order of the "cost" (i.e. for 2 failures I can't combine the cost of failure of a first line with the third line on the same factory).

The idea is that I came up with all the options, given a number of failures (in this case, from 1, to all the 9 production lines failing).


I have seen in this forum the following solution for a combination between 2 lists but that will not work in my case, due to the fact that my combinations have to follow an order:

"Suppose the A and B data start with row 1.

Then, in some empty cell, say F1 enter the formula =IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1)). Copy F1 down col. F until you get cells that look empty."

Many thanks in advance for the help!

Best,
Anao

 

[Tyron]

Hi,

I am trying to write a formula (without Macros, queries, or VBA) to create all the combinations from a list, given certain conditions.

I have several plants running several production lines, and if a production line breaks it has a cost as described below:

[TABLE="width: 551"]
<tbody>[TR]
[TD]Factory[/TD]
[TD]Failure of the first line[/TD]
[TD]Failure of the 2nd line[/TD]
[TD]Failure of the 3rd line[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10.000[/TD]
[TD="align: right"]20.000[/TD]
[TD="align: right"]n.a.[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12.000[/TD]
[TD="align: right"]1.200.000[/TD]
[TD="align: right"]n.a.[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]13.000[/TD]
[TD="align: right"]1.300.000[/TD]
[TD="align: right"]n.a.[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]19.000[/TD]
[TD="align: right"]95.000[/TD]
[TD="align: right"]1.900.000[/TD]
[/TR]
</tbody>[/TABLE]

As you can see above, the order in wich each line breaks makes a difference in the cost (i.e. the same line will have a different impact in production if it is the first, second or third line to break in that factory).

I am trying to come up with a formula that will give me authomatically all the potential combinations from my list, given a certain number of failures (I will then average those values to understand the expeced cost of failure of each number of failures).

For example, the formula should give me for 1 failure the following combinations: 10.000, or 12.000, or 13.000 or 19.000.
For 2 failures these would be the combinations:
10.000+20.000, 10+12.000, 10.000+13.000, 10.000+19.000,
12.000 + 1.200.000, 12.000 + 13.000, 12.000 + 19.000
13.000 + 1.300.000, 13.000 + 19.000
19.000 + 95.000

The trick here is the combinations always have to follow the order of the "cost" (i.e. for 2 failures I can't combine the cost of failure of a first line with the third line on the same factory).

The idea is that I came up with all the options, given a number of failures (in this case, from 1, to all the 9 production lines failing).


I have seen in this forum the following solution for a combination between 2 lists but that will not work in my case, due to the fact that my combinations have to follow an order:

"Suppose the A and B data start with row 1.

Then, in some empty cell, say F1 enter the formula

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

. Copy F1 down col. F until you get cells that look empty."

Many thanks in advance for the help!

Best,
Anao

Hey anao_,

Just to clarify a couple things so I am understanding you correctly -

1. Using just a regular Excel formula you want to determine the cost of maintenance based on what lines need to be repaired/money lost in a specified factory?

2. You already know what the cost is for each of the different combinations, but you want Excel to calculate it for you?

3. Excel's calculations are restricted to calculate in a specific way based on which lines break?
So if Line 1 and 2 in factory 1 break it is 10,000 + 20,000 for a total cost of 30,000.
If Line 1 and 3 in factory 4 break it is 19,000 + 95,000 +1,900,000 for a total cost of 2,014,000 (based on: "(i.e. for 2 failures I can't combine the cost of failure of a first line with the third line on the same factory).")

First glance would indicate to me that using helper cells or a "Cost" Sheet with the costs already entered would be helpful.

later

Ty