All possible combinations between n columns and then find and multiply the corresponding values for every combination.

[icatalin32]

Consider this: i have 3 columns ,each contains n rows with text information (not numbers). For every column this is also a column next to it with the corresponding values (factors) of each text.
What i want to do is :
- bring in another column(pivot,sheet,etc) every combination of those 3 columns of text data but in the same time bring another column to the right with the multiplied values of the corresponding combination on the left column. Say we have "21.male.worker" combination and next to it i want to bring and multiply the corresponding factors (values) like this: 21.male.worker : 0.75x0.12x1.34 or 34.female.doctor : 1.23x4.5x0.23
Is it possible in excel 2016?

 

[JamesCanale]

This would be a lot cleaner in 365 - maybe 2021 too.

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Parameter 1	Multiplier1		Parameter 2	Multiplier2		Parameter 3	Multiplier3		Parameter 1	Parameter 2	Parameter 3	Product
2	Male	0.2		Worker	1.2		21	0.1		Male	Worker	21	0.024
3	Female	0.3		Doctor	1.3		22	0.2		Male	Worker	22	0.048
4	Dog	0.4		Farmer	1.4		23	0.3		Male	Worker	23	0.072
5	Cat	0.5		Welder	1.5		24	0.4		Male	Worker	24	0.096
6				ComputerGuy	1.6		25	0.5		Male	Worker	25	0.12
7				Chef	1.7					Male	Doctor	21	0.026
8										Male	Doctor	22	0.052
9										Male	Doctor	23	0.078
10										Male	Doctor	24	0.104
11										Male	Doctor	25	0.13
12										Male	Doctor	21	0.026
13										Male	Farmer	22	0.056
14										Male	Farmer	23	0.084
15										Male	Farmer	24	0.112
16										Male	Farmer	25	0.14
17										Male	Farmer	21	0.028
18										Male	Farmer	22	0.056
19										Male	Welder	23	0.09
20										Male	Welder	24	0.12
21										Male	Welder	25	0.15
22										Male	Welder	21	0.03
23										Male	Welder	22	0.06
24										Male	Welder	23	0.09
25										Male	ComputerGuy	24	0.128
26										Male	ComputerGuy	25	0.16
27										Male	ComputerGuy	21	0.032
28										Male	ComputerGuy	22	0.064
29										Male	ComputerGuy	23	0.096
30										Male	ComputerGuy	24	0.128
31										Male	Chef	25	0.17
32										Female	Worker	21	0.036
33										Female	Worker	22	0.072
34										Female	Worker	23	0.108
35										Female	Worker	24	0.144
36										Female	Worker	25	0.18
37										Female	Doctor	21	0.039
38										Female	Doctor	22	0.078
39										Female	Doctor	23	0.117
40										Female	Doctor	24	0.156
41										Female	Doctor	25	0.195
42										Female	Doctor	21	0.039
43										Female	Farmer	22	0.084
44										Female	Farmer	23	0.126
45										Female	Farmer	24	0.168
46										Female	Farmer	25	0.21
47										Female	Farmer	21	0.042
Sheet7

Cell Formulas
Range	Formula
J2:J121	J2	=INDEX(Table2[Parameter 1],INT((ROW(INDIRECT("1:"&ROWS(Table2)*ROWS(Table3)*ROWS(Table4)))-1)/(ROWS(Table4)*ROWS(Table3)))+1)
K2:K121	K2	=INDEX(Table3[Parameter 2],INT((MOD(ROW(INDIRECT("1:"&ROWS(Table2)*ROWS(Table3)*ROWS(Table4)))-1,ROWS(Table4)*ROWS(Table3))+1)/ROWS(Table3))+1)
L2:L121	L2	=INDEX(Table4[Parameter 3],MOD(ROW(INDIRECT("1:"&ROWS(Table2)*ROWS(Table3)*ROWS(Table4)))-1,ROWS(Table4))+1)
M2:M121	M2	=INDEX(Table2[Multiplier1],INT((ROW(INDIRECT("1:"&ROWS(Table2)*ROWS(Table3)*ROWS(Table4)))-1)/(ROWS(Table4)*ROWS(Table3)))+1)*INDEX(Table3[Multiplier2],INT((MOD(ROW(INDIRECT("1:"&ROWS(Table2)*ROWS(Table3)*ROWS(Table4)))-1,ROWS(Table4)*ROWS(Table3))+1)/ROWS(Table3))+1)*INDEX(Table4[Multiplier3],MOD(ROW(INDIRECT("1:"&ROWS(Table2)*ROWS(Table3)*ROWS(Table4)))-1,ROWS(Table4))+1)
Dynamic array formulas.

 

[icatalin32]

Wow, wow,wow. You're my lifesaver, thank you very very very much. This is exactly what i needed. Ill check this sunday when i get back home and continue work analysis. The problem is that i really dont know right now how many main columns i might have and of course thei multiplier columns too.

 

[icatalin32]

Did u named/defined tables: gender as table2, profession as table3 , and age as table4? What parameters should i change if i want to add more tables (parameters and multipliers)

 

[JamesCanale]

That is how I named the tables (Excel named them really), naturally you can give the tables better names.

Adding more parameters makes the formulas more complex. I'd more and more want to use 365 for that. But mostly, the "row(indirect" bit has to be the product of rows of all tables. So in this example it's 4x6x5. If you added another table, it would be 4x6x5x?. Then you have to add another layer of MOD and INT functions to get the progression through all of the possible combinations. Adding one more layer would be easy enough. Adding many layers would get more and more out-of-hand. I've done this for as many as 7 or 8 parameters, but it gets incomprehensible.

 

[icatalin32]

That is how I named the tables (Excel named them really), naturally you can give the tables better names.

Adding more parameters makes the formulas more complex. I'd more and more want to use 365 for that. But mostly, the "row(indirect" bit has to be the product of rows of all tables. So in this example it's 4x6x5. If you added another table, it would be 4x6x5x?. Then you have to add another layer of MOD and INT functions to get the progression through all of the possible combinations. Adding one more layer would be easy enough. Adding many layers would get more and more out-of-hand. I've done this for as many as 7 or 8 parameters, but it gets incomprehensible.

I see, the problem is there could be even 12-20 tables to define, it depends of how many segments i need in my analysis. And bad news, i have only excel 2016. Why do you say in 365 (the anual fee Excel - no need for versions) it is better?i have at home excel 2021. Does it help more?it could be easier in 2021?
Thousands tx,
Kat.

 

[JamesCanale]

2021 would be good too. I would use the LET function to make the formula's a little easier to follow. 12-20 tables of any length would have an enormous set of permutations if you wanted to look at them all together. If you had just 3 items in each of 15 tables, you have 14million permutations. More than excel rows can handle.

 

[icatalin32]

I checked now and i have 3 situations, each of those require all permutations:
Situation 1 - upto 6 tables (each of max 80 records - some tables way less)
Situation 2 - upto 5 tables (the same max 80 records /table - some tables way less)
Situation 3 - upto 3 tables (each of max 12 rows).

 

[JamesCanale]

Situation 3, you have up there. It'll work just fine if you add more items in the three tables. Sit 2 is doable, very much depending on how many are in your tables. Sit 1 gets out of hand for this solution unless most of your tables are pretty small.

MrExcelPlayground17.xlsx
	Z	AA	AB	AC	AD	AE	AF	AG
1	Situation	Table1	Table2	Table3	Table4	Table5	Table6	Permutations
2	1	10	5	10	5	10	80	2,000,000
3	2	10	10	5	5	80		200,000
4	2	80	80	80	80	80		3,276,800,000
5	3	12	12	12				1,728
Sheet7

Cell Formulas
Range	Formula
AG2	AG2	=AA2*AB2*AC2*AD2*AE2*AF2
AG3:AG4	AG3	=AA3*AB3*AC3*AD3*AE3
AG5	AG5	=AA5*AB5*AC5

 

[icatalin32]

Now i see. Bad news. I'll stick with situations 2 and 3 and put aside situation 1 for now,at least on ms excel. Ill try to adapt what you provided for situation2 (max 5 tables, max 80 records, only 2 tables could reach 80 records, the other 3 at max 15-30 records each).
Thank you again very very much.