Recommend
2 
 Thumb up
 Hide
100 Posts
1 , 2 , 3 , 4  Next »   | 

Rolling Stock» Forums » Play By Forum

Subject: Spreadsheet improvements rss

Your Tags: Add tags
Popular Tags: [View All]
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
I'm starting this thread to discuss possible improvements to the spreadsheet being used to host the PBF games.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
sparr0 wrote:
I'm starting this thread to discuss possible improvements to the spreadsheet being used to host the PBF games.


Tell me all your ideas!
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
First, I'd like to suggest global conditional formatting to put the correct background color on company names.

I believe that the following conditional format formulas can be applied to A1:Z1001 to correctly identify the red, yellow, etc companies:

=NOT(ISERROR(SEARCH(CONCATENATE("|",A1,"|"),"|BME|BSE|KME|AKE|BPM|MHE|")))
=NOT(ISERROR(SEARCH(CONCATENATE("|",A1,"|"),"|WT|BD|BY|OL|HE|SX|MS|PR|")))
=NOT(ISERROR(SEARCH(CONCATENATE("|",A1,"|"),"|DSB|NS|B|PKP|SNCF|KK|SBB|DR|")))
=NOT(ISERROR(SEARCH(CONCATENATE("|",A1,"|"),"|SJ|SZD|RENFE|BR|FS|BSR|E|")))
=NOT(ISERROR(SEARCH(CONCATENATE("|",A1,"|"),"|MAD|HA|HH|HR|LHR|CDG|FRA|FR|")))
=NOT(ISERROR(SEARCH(CONCATENATE("|",A1,"|"),"|OPC|RCC|MM|VP|RU|AL|LE|TSI|")))


Those six conditional formats would need to be applied to each sheet that can have company names on them. This could replace the vlookup being used in the color column of the Deck and Companies sheets, and would enable automatic coloring on the Players and Corporations sheets.

PS: sadly, google docs doesn't allow a conditional format formula to refer to a different sheet, or else the formulas could use a vlookup pointed at the companies sheet like the deck sheet currently does.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
'tis beautiful.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
Next, a question in prelude to a suggestion... Why were the synergies implemented as a single column of two concatenated numbers, instead of two columns of numbers or company names?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
Actually, I'd rather code it by face value number.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
sparr0 wrote:
Next, a question in prelude to a suggestion... Why were the synergies implemented as a single column of two concatenated numbers, instead of two columns of numbers or company names?


I didn't write that at all, that was Galgor. When I did it, I listed them off to the side (as we have here) as the names of the companies, and used a big huge formula to evaluate synergies. It worked great! I don't know what Galgor was doing.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
Jythier wrote:
Actually, I'd rather code it by face value number.


Implementing this in practice on the Corps sheet and the Player sheet will probably be a pain.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
I'm trying to write a formula to pull the synergies for the particular companies in a game over all in a line while not leaving blank spaces for synergies that don't show up. It's not going well.

So far I have:

=index(Synergies!$C$1:$C$274,match($C2,Synergies!$B$1:$B$274,0)+arrayformula($I$1:$P$1))

Which pulls over the first number for the synergies only. Ugh.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
Jythier wrote:
I'm trying to write a formula to pull the synergies for the particular companies in a game over all in a line while not leaving blank spaces for synergies that don't show up.
Can you give me an example of what the result should look like?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
The synergies on the Companies in Game sheet.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
So for BSE it should return BPM and MS.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
Is there a reason that the canonical synergy source can't be the version with one row per company and one column per synergy, like the Companies in Game sheet?

Does something benefit from the one-column or two-column format that might be on the Synergies sheet?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
There isn't.

I wrote this:
=iferror(index($A$2:$A$25,(match(index(offset(Synergies!$B$1,match($C2,Synergies!$B$1:$B$274,0)-1,1,countif(Synergies!$B$1:$B$274,$C2),1),1+I$1,1),$C$2:$C$25,0))),ʺʺ)

But it relies on numbers at the top of the spreadsheet to work right, and it leaves lots of blanks.

https://docs.google.com/spreadsheets/d/1nJ0CGI1517JIy1CMfXpG...
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
We filtered and conquered. Yay.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
J C Lawrence
United States
Campbell
California
flag msg tools
designer
badge
Avatar
Formulae will only take you so far. Javascript embedded in the Google Sheet can also be really useful for this sort of stuff, especially if you want things to move/update in odd ways.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
clearclaw wrote:
Formulae will only take you so far. Javascript embedded in the Google Sheet can also be really useful for this sort of stuff, especially if you want things to move/update in odd ways.


Yeah, we have that going on too. It's just a little farther out of my wheelhouse. Getting better at it though!

We have an income script, a dividend script, a phase 4 & 5 cleanup script...

Sort sorted the blanks to the beginning instead of the end, but filter worked really well and now everything is linked and should be workable for setting up new games.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
I haven't tried JS in a Google Doc. The macros in this sheet are my first exposure to them. I'll be checking that out.

Jay, is there a reason companies are not listed in face value order in the spreadsheet company lists?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
sparr0 wrote:
I haven't tried JS in a Google Doc. The macros in this sheet are my first exposure to them. I'll be checking that out.

Jay, is there a reason companies are not listed in face value order in the spreadsheet company lists?


They're in order of appearance on that sheet. Wouldn't be a big thing to change them.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
I think they would look cleaner in face value order. There would be a nice stair-step effect in the synergy columns, income and ROI calculations would have more smooth progressions, etc.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
Manual process but done!
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
Eventually, have it roll the companies in the game itself based on # of players.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Sparr Risher
United States
San Francisco
California
flag msg tools
designer
Avatar
mbmbmbmbmb
ok, next up... the companies sheet has an Owner column. Do you want that to be the canonical location for that info, and the Players sheet to pull from there, or vice versa?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
sparr0 wrote:
ok, next up... the companies sheet has an Owner column. Do you want that to be the canonical location for that info, and the Players sheet to pull from there, or vice versa?


Companies in game, to my mind, is a reference sheet.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
Jythier wrote:
sparr0 wrote:
ok, next up... the companies sheet has an Owner column. Do you want that to be the canonical location for that info, and the Players sheet to pull from there, or vice versa?


Companies in game, to my mind, is a reference sheet.


I go to Players to see what a player has and to Corporations to see what Corps have, but if I'm looking at what a corp and I have and the synergies, I would love to see that on CIG.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
1 , 2 , 3 , 4  Next »   | 
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.