Thread Rating:
  • 2 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Playability Spreadsheet
#1
[attachment=290]A couple months ago, Andrew posted the file that shows the interdependencies between PG games and supplements. I took that file and created what I hope is a useful spreadsheet. The sheet allows the user to add the games and supplements that they own and see how many of the scenarios are playable.

The sheet requires one to enter on the top row (from column G on to the right) a "yes" for those games you own in the appropriate column. It then automatically calculates the total number of playable scenarios you have, how many senarios depend on a given game/supplement and how many scenarios of a given game/supplement you can play given the inventory you have entered.

One thing I've not figured out how to do is the "what if" of which supplement increases the playable scenarios the most. I have an idea on how to do it, but would like to gauge interest in such a tool first. NEW! The Macro-enabled version of the sheet will now calculate the number of scenarios you can play if you were to add each of the games not already owned.

The big downside to this is that the file is big (over 6MB Ver 1-1 is over 7.9MB). I created it using Excel '10, and while I didn't use macros, I cannot guarantee the conditional formatting will work in other spreadsheets.

NEW for Version 1-1: All scenarios are now hyperlinked to PG-HQ. If you click on the scenario hyperlink (Column B) it will take you to the corresponding page. If you click on the Game Hyperlink (row 6) it will take you to the corresponding Game page. ENJOY!

NEW for Version 2: xlsx file has Saipan 1944 added. The xlsm file calculates using a macro how many more scenarios you can play if you add any game you don't currently own.
[color=#32CD32]

Dean


Attached Files Image(s)
   

.xlsx   PanzGren Scenario Playability - Ver 2.xlsx (Size: 7.97 MB / Downloads: 45)
.xlsm   PanzGren Scenario Playability - Ver 2 - macro.xlsm (Size: 8.54 MB / Downloads: 43)
Reply
#2
(11-09-2012, 10:56 AM)Dean_P Wrote: Even so, with the size I'd rather not post it here until Andrew assures me it won't break the system.

Post it! I pay for "unlimited" bandwidth, let's make the bastards regret it!

   
...came for the cardboard, stayed for the camaraderie...
Reply
#3
I'm delighted you took the trouble to do this. Someday we'll have such functionality on the site itself, but your efforts have provided an outstanding stopgap that will allow me to focus on other high-profile things for awhile.

Everybody use Dean's awesome spreadsheet!

+2 to you, sir! Big Grin

edit to add - I have stuck this to the top of the forum and will leave it there until we implement an online replacement, good show mate!
...came for the cardboard, stayed for the camaraderie...
Reply
#4
Here's the sheet... Enjoy!

A couple more notes:
1) The "yes" that are already in the top row are my inventory, and are just for example. Put your collection in and have fun.

2) Typing in any cell other than the top row could make the sheet unusable. I hid a lot of text (white on white) to make things easier to see. I also hid several rows, again to make it less cluttered.

By the way, I'm almost immune to criticism, so if it doesn't do what you want, let me know. I promise not to take it personally...

Also, if you find an error, please let me know!

Oh, and I must tell you... there are some things I HATE about excel... but that's another thread!

I've moved the file to the first post... enjoy!
Reply
#5
Some more "user guide" notes...

If you're accoustomed to using groups in Excel, the left hand margin will be familiar. If not, clicking on the plus signs will expand that individual group. i've grouped each game and each scenario in that game. If you click on the numbers in the upper left of the sheet area (1,2, 3 or 4), you'll expand everything to that level. Note that there are almost 6000 rows, so clicking on the 4, and expanding each scenario fully, may cause things to slow down (and you might even see "not responding" in the excel window bar). Be patient, padowan, and the program should respond.

If you look under the "Playable" column, and have expanded down to the "scenario" level, the color coding indicates whether that particular scenario is playable. Green is yes, reddish rose is no. Expanding that shows the games required for that scenario, but doesn't indicate which one you don't have. Sorry - couldn't figure out how to get Excel to do that one. The same cannot be said at the Game level. Just because the "playable" is green, it doesn't necessarily mean that you can play all the scenarios...

Andrew, as for adding rep, I appreciate it, but it may have been better to do so after messing around with the sheet for a bit.
Reply
#6
NEW for Version 1.1

All scenarios are now hyperlinked to PG-HQ. If you click on the scenario hyperlink (Column B) it will take you to the corresponding scenario page. If you click on the Game Hyperlink (Row 6) it will take you to the corresponding Game page. ENJOY!

Dean
Reply
#7
Well done
Reply
#8
I have updated the spreadsheet, including Saipan 1944 in the inventory - file name is "PanzGren Scenario Playability - Ver 2.xlsx"

That alone wouldnt' have been worthy of a 2.0 listing, but this next addition I do think is worthy. I have added a short macro that calculates how many additional scenarios you can play by adding each of the games in the series. The name of that file is "PanzGren Scenario Playability - Ver 2 - macro.xlsm".

When you open the file, Excel asks if you want to enable the content. Say "yes". Once you have enabled the content, clicking the "Calculate" button in A4 will start the macro and it will cycle through the games, calculating how many more scenarios are playable and the number is placed in row 4 of each game.


Attached Files Image(s)
   
Reply
#9
(11-09-2012, 11:35 AM)Shad Wrote:
(11-09-2012, 10:56 AM)Dean_P Wrote: Even so, with the size I'd rather not post it here until Andrew assures me it won't break the system.

Post it! I pay for "unlimited" bandwidth, let's make the bastards regret it!

LMAO!
Reply
#10
I've added an Open Office version of the basic spreadsheet (no macros). It was created with Open Office 3.3 for Windows, and is stored in the .ods format (native open office). Hope that works for those with no access to Excel


.ods   PanzGren Scenario Playability - Ver 2.ods (Size: 4.19 MB / Downloads: 11)
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)