digger1,
Welcome to the forum!
I found this thread and the challenge presented within it interesting, so I gave it a shot.
Attached is an example workbook based on the criteria you described.
It uses dynamic named ranges and a helper column to get the correct permutations depending on the number of sites listed in column A. As MrShorty has already suggested, I also prefer to have information listed in separate cells rather than combined into a single cell.
The first named range is named list_Sites and it is defined with this formula:
The second named range is named list_Slopes and it is defined with this formula:
Next I used column M as a helper column. I have hidden the column, and you can unhide it if you'd like to see it. In cell M1 is the number 0. This is used to start the helper column. In cell M2 and copied down to cell M15 is this formula (I only copied to cell M15 to keep file size low. This allows you to have up to 15 sites and the permutations will auto-generate properly. To have more, copy down to a row number that is likely more than the maximum number of sites you'll have):
So, with all that setup, we can create the formula to auto-generate the correct 2-site permutations based on the number of sites listed in column A. So, in cell D2 is this formula:
In cell E2 is this formula:
Those are both copied down to row 106. The reason for row 106 is because from row 2 to 106 is 105 rows. 105 is the number of permutations if you had 15 sites. If you are going to have more sites, copy the formulas down further to accomodate the maximum number of permutations depening on the maximum number of sites.
Lastly, in cell F2 and copied down is this formula to get the absolute value of the slope difference:
Bookmarks