Handy Excel-based Scenario analyzer (using data from ROAR)

Handy Excel-based Scenario analyzer (using data from ROAR) 2.2

No permission to download
Repeat: Don't use version 2.1. The ratings column didn't work in that version.
ASLers,

If the version that you have is no longer working with ROAR and cut-and-pasting data into the sheet does not work, try this new version.

If you have any problems, let me know.

Ken
For those of you who wish to add more/new scenarios to the workbook, there are two ways to do it.

Which way you choose depends on your skill level within Excel.

Option 1: If you don't know how to use formulas within Excel, you can send me a PM and request certain modules to be added. I can easily do so and am happy to help, as long as the number of scenarios isn't too lengthy...

Option 2: If you know a moderate level of how to use formulas within Excel, you should be able to add more scenarios yourself. The basic steps are:

1. Unlock the sheets. No password is needed. Just click "Unlock Sheet" for each sheet under the "Review" tab.​
2. Unhide the two hidden sheets. Right click on the sheet tabs near the bottom of the Excel window and select "Unhide" for the two hidden sheets that are listed. The "Input Data" sheet (one of the hidden sheets) is just some basic instructions on how to fill in the data for each scenario. The "Data" sheet (the other hidden sheet) is the one that you will actually use.​
3. On the "Data" sheet, add the scenarios that you wish. This is done as follows:​
a. Manually type the scenario name in column C. Be sure to use exactly the name as it appears in ROAR.​
b. Add all the scenario information (as instructed on the "Input Data" sheet) in columns E-Q (the white colored columns).​
c. Copy-and-paste (drag) the formulas in all the other columns (namely: A,B,D,R-X). Note column S (ASL Scenario time to play) is not used, so can skip that column.​
4. Go to "Setup" sheet and unprotect it (on Review tab) if needed. Add the name of the new module and copy-paste the yes-no pull-down box from an existing one. Determine which cell the new yes-no box is in (e.g. J64 is the next cell at the bottom right below the existing box, shown below). Ensure it is selected to be "yes". An example of how this looks is shown below. Note: You can choose to put the new yes-no box anywhere. I suggest starting with the lower right corner as shown. Add additional modules below that, or to keep it looking nice, below Festung Budepest:​
9890
5. Go to the "Data" sheet. For all the new scenarios in this new module, change the formula for that scenario row in column B to match the new cell (e.g. $J$63 change to $J$64 - or whatever cell you used for the new yes-no box in Step #4). There is only one instance of this cell being mentioned in the formula. Find it and change the "$J$63" to whatever cell your new yes-no box is located in. Do not remove the two "$" that are interspersed within the J and 63.​
6. Once completed, the Data sheet should look as below (I only show adding the first two scenarios of the new WO Bonus Pack using bogus info in columns E-Q in this example:​
9891

You should see a numbering in column B (increases by one each scenario, including spaces. #1283-1286 in the illustration above). If you select "No" on this module on the "Setup" sheet, these numbers disappear. The "Est Time to play" in column R should automatically calculate the length of the game, based upon the info that you added in columns E-Q. You can add the ASL Scenario Archive time to play in column S if you want. I used this only as a checksum to ensure that my formula in column R was accurate. All the other columns should automatically add data used elsewhere - you don't need to do anything with them.

Be sure that the formulas in column A and X are copied and pasted from the existing rows above. If all this is done correctly, your newly added scenarios should appear (when the "yes" is selected on the Setup sheet) on the Summary sheet, as shown below. If the scenarios are listed in ROAR, the data should also automatically appear:
9892


Hope that those instructions were clear enough.
Reactions: ASLAddict
Top