Help with a spreadsheet for predictive pricing of goods

Spoiler

Hi there,

I've had this game in my steam library for ages and just started playing it and I'm trying to make sense of the trading system and learning how to use some intermediate/advanced functions in excel (google sheets) and was looking for some feedback.

​

I found a post on the steam forum that broke down the modifiers on each station by system, planet, economy type, and event. I converted the up and down arrows to a scale from +1 to -1. Atm I'm using named ranges so that each line can be referenced using Indirect(). In sheet1 I enter the data I have about the system/ planets using restricted dropdowns to make sure the data matches the named range, this populates a corresponding range in "sheet1 mirror" with the (usually) correct data. The trade goods section of "sheet 1" then adds up the cells for each product (with a 2x multiplier on system because that seemed to be more important). The data in the trade goods section of sheet 1 has conditional formatting to show whether the stations should be above or below average.

​

Known Bugs:

  1. If you delete a row or column you get a reference pointer error even if you have data in the replacement row: deleting row 13 causes all indirect(sheet1:???13) references to fail.

​

  1. sorting the sheet causes unexpected behavior, Mirror updates for the newly sorted sheet 1, but sheet 1 calculations refer to the pre-sorted line resulting in incorrect calculations

​

TLDR:

Overthinking trade and using the game to learn Excel (re: google sheets) a bit. Could use advice for better ways to set up the spreadsheet so it's less clunky or other inputs from the game to make the sheet work better.

​

Link to the sheet:

https://docs.google.com/spreadsheets/d/17Zwk5RVI7alo0Lw2OHLefX0gVfMaxkFguFEU2zREWs0/edit#gid=0

​

Link to Steam forum thread (Thanks to Draconus ):

https://steamcommunity.com/app/290300/discussions/0/1742220359689882589/

EDIT: Link to the source material (sort of, was difficult to access directly)

https://docs.google.com/drawings/d/10QIXsSSrBKmW18DRKeL96qbhQ8kcy7jtOGFhM6WrL1A/edit

​

https://i.redd.it/qw4wzsx8kc331.jpg

Posted Jun 9, 2019, 3:11 PM UTC · edited

213
rmoser2000•

Okay, see if this makes sense to you. Looking at your 'Sheet 1 Mirror', it seems like all the +/- modifiers depend only on the characteristics in cols D-G on Sheet1. If that's true, then you don't need a separate row for each Planet on that sheet. If you want to know the price for Alien Artifacts on row B, you just need to sum the modifiers for the four characteristics: Planet Type Pelagic, Economy Bureaucratic, System Type Mineral, Event NONE.

There's a function called VLOOKUP that would make this much easier:

VLOOKUP(Key Value, Table, Column Number, Is Sorted)

Key Value is a string (or number)

Table is a Range, where VLOOKUP will look in the first column of that Range and find the Row that matches your Key.

Column Number tells which column from that Row to use as the return value. 2 would returns values from the second column of the Table

Is Sorted tells how to define a match to the Key Value. You want FALSE, otherwise you'll have to sort the characteristics.

​

You could make one table for each characteristic, OR you could make ONE table including ALL the characteristics. A single key column (characteristic), where each data column is the +/- modifier for one of the goods. Then your function on the main table is, in pseudocode:

VLOOKUP(Planet Key, Sheet 1 Mirror!A:BA, Column Number, FALSE)

If the Planet names are unique across the galaxy, then you can just use that for the Key. If they are not unique, then you can concatenate the System and Planet name to make the lookup key.

You either manually force Column to match the Good that you're calculating, or you can use a similar function (either MATCH or HLOOKUP) to search the column names and find the matching column name.

​

So if you make one table to store the modifiers for all characteristics, then the final function could look something like this (formatted for clarity):

$D$1 * VLOOKUP(Planet Type, Table, Col, FALSE) +
$E$1 * VLOOKUP(Economy, Table, Col, FALSE) +
$F$1 * VLOOKUP(System Type, Table, Col, FALSE) +
$G$1 * VLOOKUP(Event, Table, Col, FALSE)

​

As for INDIRECT, it's not even supposed to work the way you're using it - this looks like a Google Sheets bug, but it's definitely undocumented behavior. INDIRECT expects a string that represents a cell reference. INDIRECT("Sheet1!A1") will always land on that cell, because even if you delete row A, another row will now be row A.

​

Good luck, and have fun!

3
Buffinator360OP•

Thanks for the suggestion! I've heard of VLoopup but never tried to use it before. I'm working on implementing it now.

1
Buffinator360OP•

Here is what I have so far. This is so much better it's unbelievable. I was up to around a 20second load-time whenever I made changes on the previous one!

https://docs.google.com/spreadsheets/d/1PqncLmrGrL59zpyledleU8WMSLAKt9DQcaW7gEy02lM/edit#gid=0

=($D$1*VLOOKUP($D3,DataSet!$A$2:$AA,H$1,FALSE))+

($E$1*VLOOKUP($E3,DataSet!$A$2:$AA,H$1,FALSE))+

($F$1*VLOOKUP($F3,DataSet!$A$2:$AA,H$1,FALSE))+

($G$1*VLOOKUP($G3,DataSet!$A$2:$AA,H$1,FALSE))

​

I ran into two problems when making the change.

First, input 3 didn't increment when expanding the formula, so I numbered the columns to select the correct row. I'm looking into changing this to Column() after I play around with it a bit.

Second, condensing my 4 sheets (Econ, System, Events, PlanetType) into one sheet broke my data validation on my drop-down menus. I couldn't figure out how to get the lists to point to the range I wanted and also point to "none" if it wasn't continuous. I ended up creating a second row "None" so that all 4 lists would be continuous with an instance of "None" so I would have a valid placeholder value.

​

Edit: Updated with Column() function instead of numbering the rows

https://docs.google.com/spreadsheets/d/19SJRtRfkpIoavCgmyXi7VJV4FiztZ-NlGNyh96k28EI/edit#gid=577691690

1