Dynamically multiplying columns together for all combinations

[JordanJ]

Hi all,

I've been stuck on this for a while now and can't find anything on the discussion board on issue. 

I have 5 tables, and each have a number (in the multiple column) for a given year or scenario. I've successfully created a list of all possible combinations that can be created from these tables, but my question is how do I then create a list of all combinations with the percentages in the 'multiples' columns multiplied together?

For example, let's say the calendar year is 2016, the class is Preferred Plus NT, the face amount is $0-$249,999, the issue year is 1-2, and the next issue year is also 1-2, so I'd have 1.1% * 2.1% * 4.1% * 5.1% * 6.1% for that scenario. I then want to calculate the same thing for all possible combinations that can be created from the five tables.

I was going to brute force this with a formula tool, but I was hoping there is a more elegant way, and one that would account for the percentages being updated in the spreadsheet. Any ideas on how to do this?

Thank you!

[Inactive User]

Not super clear what you are trying to do but here is my recommendation. if you have 5 tables, structure them as "Name" and "Percentage", Each row represents some item/thing and the percentage application. Then you append all 5 tables together which will create a grid of all possible combinations. But that will also bring the percentages into the mix too. You then set a formula of Percentage 1, 2, 3, 4 and 5 multiplied together. Done.

[danilang]

Hi @JordanJ 

Can you share your input file, "possibilities.xlsx", as well?

Dan  

[JordanJ]

Hi Dan,

Here is the input file.

[danilang · Accepted solution]

Hi @JordanJ 

Like @Inactive User said.  You have 5 tables here so treat them as such.  

For each table, use a Select tool to take only the columns you need.  Filter out the null rows.  If the table has an Issue Years field then you need to split the set in 2: one set <= Cut off year and after the cut-off year and append these values with the calendar year table which has also been split based on the cutoff year.  The append tool will automatically generate all the possible combinations for you.  Union the pre-cutoff and post-cutoff record  This handles the Class and Policy year tables.  The Policy Year 2 and Face Amount Band don't have year ranges associated with them so just append the values.  Since you now have all the combinations, the final formula tool just applies one formula to all the rows resulting in

Dan