Soldarius
Deadman W0nderland
The 99 Percent
864
   |
Posted - 2014.11.03 21:10:39 -
[1] - Quote
I copied your spreadsheet so I could play with it. Link is below.
https://docs.google.com/spreadsheets/d/1L_2H8rGawc8IJ64DS9hu7Sr7OGo5d-kf6YMT8APGIVQ/edit?usp=sharing
Goggle Spreadsheets is no longer limited to 50 import calls. But that doesn't mean you should drop a thousand into it. Fortunately, there is a way around having to type all that out.
"Join" is used to join a bunch of things together using a common delimiter. Very useful for turning an array of values into a comma separated string, which is what you need for price calls to eve-central. Something about Microsoft router software not liking long strings. But it does nothing for a single value. For a single price call, just use the cell reference, not Join. Check the service's API documentation to see if and how many values it will take at once.
Use named ranges to make referring to commonly referenced ranges like your price sheet or a recipe sheet much easier. This way if you make a change to your price sheet or some other long list of things you don't need to go through the entire spreadsheet looking for references in formulas. Just change the range dimensions in your named range. You can find this feature under "Data > Named Ranges..."
Rather than making a column really narrow to hide it, just use the Hide feature. Literally right click on the column head, click "Hide Column". A small icon will become visible in the column headers where the hidden column would be. Clicking on that icon will reveal the column.
When you need to do this: =sum(H4*D4)+(H5*D5)+(H6*D6)+(H7*D7)+(H8*D8)+(H9*D9)+(H10*D10)
Do this instead:
=sumproduct(h4:h10,d4:d10)
Summing the product of 2 declared values is pointless. Instead of using sum(x*y)+x, just use x*(1+y). Its a standard formula for increasing something by a variable percentage, which is very common in Eve. No need to make formula calls here. Just do math.
I changed some of your formulas to make it much easier to add and remove items from your prices sheet, as well as to more easily deal with multiple calls. ArrayFormula is your friend. So rather than having to edit a formula on every row of a large list of things, you just change the one formula up top and voila! Everything in the entire list updates at once, and with only one function call. This makes your spreadsheet a lot faster.
I really like what you did to allow for multiple simultaneous selections with mineral quantities for each run of ships. Personally, I would have put the recipes and mineral requirements on their own sheets. But that's just me. I did change the Tritanium column from individual formulas to a single array formula. Its a simple example that shows how powerful array Formulas can be. It also clearly demonstrates how to specify a entire column or row as an array range by leaving out one part of the reference.
Example: A1:A is entire column A. B1:B = entire column B. A1:1 would be entire first row. B1:1 would be first row, but starting at column B.
"Remember remember the 4th of November!"
Phoebe. Coming soon to Eve Online.
|