I would like to let everybody know that there is a new version of the Ranking System optimizer that can be found here .
This spreadsheet was produced by Ralph Nohe (ValueSeeker). He has indicated that he may produce a YouTube video regarding its use. Everyone is invited to provide comments and suggestions for future upgrades on this thread.
The optimizer has an expanded number of nodes and iterations, and also doesn’t require EXCEL to be set to manual calculation.
Description
There are 3 different tables (2 hidden between columns A-Z), the one visible is only the final result. One table is for iteration and resembles the table used in your earlier spreadsheet version. Then, there is one table for normalization, and then, there is the visible table where all non-zero numbers are shown in light green.
Using Excel values instead of formulas:
The VBA code copies the final results from one of the hidden tables into the light green visible one as values only, which is highly helpful to prevent any unitended Excel recalculation, e.g. when another excel sheet is opened or when the current worksheet is being saved. It happened to me several times, and my Excel data was lost (then I had to manually input the best permuation from P123 into Excel for the next iteration cycle, which is some work in case of 25 nodes.
Sensitivity settings:
The higher the number of nodes, and the more iterations have already been made, the lower the sensitivity can be set. Hence, for 25 nodes with an average node weightage of 1/25=0.04 a sensitivity of 20% will lead to a sufficiently high diversity distribution among the interations created. For only 3 nodes being weighted 0.33 in the beginning, only 80% will create a workable diversity.
Starting weights, and their usage for Reference:
The starting weights should sum up to 1.00, e.g. for 3 nodes: 0.20/0.30/0.50, or 0.05 each when having 20 nodes.
The starting weights are copied into the first column. Why does that make sense: lets say we have already used the first set of iterations in P123, and found a new max. permutation, the weightages of this new max. permuation should be used as starting weights for the next iteration in Excel. These can easily be copied and pasted directly from the light green Excel table into the orange fields, and are then used as iteration no. 1. So back in P123 during the next permuation this last (good) result does not get lost, and is shown as reference in new permuation no.1
Ralph - thank you very much for taking this project over.
Thanks
Steve