Pages: [1] 2 :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Nezz Jaran
|
Posted - 2010.04.29 05:33:00 -
[1]
Yes, since everyone is making PI Spreadsheets, I decided to toss mine into the mix. It's not pretty, but it's being designed to be as future proof as possible.
In this version of the spreadsheet: Names are entered once (in column A in the Reference sheet), every other instance is just a reference.
All values are calculated using multiple Index/Match iterations based on the self referencing table in the References sheet. If CCP changes the materials required for a component, change the references for required materials and everything updates automagically. The only issue is the current method (only truly dynamic method I've found so far with turning to VBA modules) does not allow non-unique components in a column. This is the reason for the funky spacing in the T2 and T3 parts of the table.
The current build rates (6000 T0 makes 20 T1, 40 T1 makes 20 T2, etc) can be modified using the chart in the upper right of the References Sheet. At present, only the values can change, not the T-levels used. This will be addressed in a planned revision.
Planned for the next version: anticipated for this weekend Make use of the T-levels and enhance the formulas to improve build quantity requirement handling. IE, If CCP decides that T3 items should require 1000 T0 materials, all that needs to happen is update the appropriate column and the spreadsheet will handle this. Right now, the formulas would need to be revised.
Time/Build Rate calculations for basic decision support.
|
Rakshasa Taisab
Caldari Sane Industries Inc. Initiative Mercenaries
|
Posted - 2010.04.29 06:10:00 -
[2]
Much more functional than others I've looked at.
BTW, T0 and T1 materials are pretty much the same, so if I select a T3 product I want to have both T1 and T0 requirements show up. (add another yellow field)
|
Nezz Jaran
|
Posted - 2010.04.29 10:59:00 -
[3]
If you expand columns M and N, i think (don't have the file handy), will that give you the info you need?
|
Baneken
Gallente Aseveljet Sev3rance
|
Posted - 2010.04.29 11:12:00 -
[4]
So the spread sheet is suppose to count the required stuff from the amount of raw materials you specify in T0? Because at least in office 2007 you spread sheet does nothing (which isn't a surprise with MS products if you used older/non MS exel).
|
Nezz Jaran
|
Posted - 2010.04.29 12:03:00 -
[5]
Originally by: Baneken So the spread sheet is suppose to count the required stuff from the amount of raw materials you specify in T0? Because at least in office 2007 you spread sheet does nothing (which isn't a surprise with MS products if you used older/non MS exel).
Used 2003. Sometimes I really dislike Microsoft. Yes, it's supposed to calculate all required components working backwards from any given items.
I've got 2007 on my laptop, I'll take a look at it there and revise as needed. I'm betting MS removed either Index() or Match().
|
Janus Ovellian
Minmatar Calpolli Namtz' aar K'in
|
Posted - 2010.04.29 12:11:00 -
[6]
Originally by: Nezz Jaran If you expand columns M and N, i think (don't have the file handy), will that give you the info you need?
Ah very nice - works all the way up to the t4 stage.
I like it.
Interesting times await... |
Nezz Jaran
|
Posted - 2010.04.29 12:31:00 -
[7]
Aside from revised formulas to better accommodate any revisions by CCP and some additional metrics about build times, what are other suggestions?
Debating on including the POS and Outpost BPOs and calcs to determine the materials required to build a user selectable POS design.
|
Mr Pikey
Caldari
|
Posted - 2010.04.29 13:45:00 -
[8]
Just to let you know it works fine in Open Office 3.2
Regards, Mr. P.
|
iP0D
|
Posted - 2010.04.29 15:42:00 -
[9]
For some reason it seems to break a bit when uploading to Google docs?
|
Nezz Jaran
|
Posted - 2010.04.29 17:55:00 -
[10]
Originally by: iP0D For some reason it seems to break a bit when uploading to Google docs?
Just tried uploading to Google Docs and it's failing for me. Don't really use Google Docs, so I'm not sure how well it handles complex formulas.
|
|
iP0D
|
Posted - 2010.04.29 17:57:00 -
[11]
It shouldn't have any issues, bit strange. Maybe there are some locked elements, or complex macros, but usually that works fine.
It's a pretty sheet I have to say, nice work. It's just, I tend to use Google Docs since it's earier to all work from a library of docs & sheets, without sending the files around each time.
|
Sjolus
Metafarmers MeatSausage EXPRESS
|
Posted - 2010.04.30 11:29:00 -
[12]
This is EXACTLY what I've been working on. AWESOME <3
9/10 in its current state. 10/10 if you include the blueprints for pos's, pos mods, sov structures, etc.
|
Irillide
|
Posted - 2010.04.30 13:16:00 -
[13]
Is there any significance to Autotrophs, Felsic Magma and Reactive Gas being bolded and coloured red? |
Nezz Jaran
|
Posted - 2010.04.30 14:26:00 -
[14]
Edited by: Nezz Jaran on 30/04/2010 14:28:04
Originally by: Irillide Is there any significance to Autotrophs, Felsic Magma and Reactive Gas being bolded and coloured red?
They can only be found on a single planet type, so I felt they should stand out a little.
Originally by: Sjolus This is EXACTLY what I've been working on. AWESOME <3
9/10 in its current state. 10/10 if you include the blueprints for pos's, pos mods, sov structures, etc.
Thanks! I'm going to be updating the spreadsheet over this weekend, I'll see if I can fit this in.
|
Sjolus
Metafarmers MeatSausage EXPRESS
|
Posted - 2010.04.30 14:48:00 -
[15]
Also, this seems to work splendidly with Google Docs for me. What parts aren't working for y'all?
|
Baneken
Gallente Aseveljet Sev3rance
|
Posted - 2010.05.01 07:03:00 -
[16]
All right I got it to work now and you we're right that I was definitely using the formulas "backwards" even if the formula should work on both ways but I guess that would require some more work.
http://desusig.crumplecorn.com/sigs.html |
Nezz Jaran
|
Posted - 2010.05.01 14:38:00 -
[17]
Edited by: Nezz Jaran on 01/05/2010 14:38:44
Originally by: Baneken Edited by: Baneken on 01/05/2010 07:17:40 Edited by: Baneken on 01/05/2010 07:09:44 Or rather it should count materials for all "tiers" and not just for T0 so you could see the whole chain of required materials or my exel is working only partially (again).
Do you get the information you're looking for if you expand cells E, F, I, J, M, and N?
I can put in another sheet that calculates things the opposite of what I have now, but it will be confusing. T0 to T1 is easy because it's a 1:1 ratio. T2 and beyond will be wonky because it's not a 1:1 mapping and the calc will show all possibilities by default.
Example: You have 12,000 of Base, Heavy and Noble metals. That converts to 40 Reactive, Toxic, and Precious metals. If I calculate up from T0 to show what can be built with materials at hand, you'll see 5 Construction Blocks, 5 Enriched Uranium, and 5 Mechanical Parts as possible final products. There is enough T0 material to build any one of those three, but not all three.
There are ways around that (weighted scoring comes to mind), but I think that's for a future revision.
|
Makar Kravchenko
|
Posted - 2010.05.01 14:54:00 -
[18]
HELLO?
.XLS files can carry payloads executed by embedded VBA code.
Just a heads up.
It would probably be better to post your formulas with example explaining how they work, rather than a link to and .xls
Thanks
|
Nezz Jaran
|
Posted - 2010.05.01 15:56:00 -
[19]
Edited by: Nezz Jaran on 01/05/2010 16:06:45 Edited by: Nezz Jaran on 01/05/2010 16:00:49 Edited by: Nezz Jaran on 01/05/2010 15:57:25 Edit: I broke the forums! Add revised phrasing a bit
Originally by: Makar Kravchenko HELLO? .XLS files can carry payloads executed by embedded VBA code.
Out of the box, Excel 2003 has Macro Security set to 'High'. High only allows signed macros from trusted publishers; unsigned macros and signed macros from untrusted publishers are disabled. Also, out of the box, there are no Trusted Publishers. I don't have access to earlier versions of office, so I can't verify the default state of macro security for Excel version prior to 2003. Excel 2007 appears to work the same way on my work laptop (but that could be local / global policy settings as well). When a macro attempts to run that is not sign by a Trusted Publisher, a very ugly early message pops up with a wall of text.
The entire sheet runs off three distinct flavor of formulas. Since VLookup only works left to right, I can't use it. To simulate a right to left VLookup, I used the Index(Match()) combination. The first is the "Carry Over" Formula, which is used to calculate materials required for the next tier (T1 looks to T2 (and T4), T2 looks to T3, T3 looks to T4. This formula will be changed to reference the values in Reference!L3:P6, allowing for dynamic change of products and quantities. The following is used by the T3 Carryover to determine requirements for T4 items:
=((IF(ISNA(INDEX($A$3:$A$10,MATCH($D3,Reference!$B$82:$B$89,FALSE))),0,INDEX($A$3:$A$10,MATCH($D3,Reference!$B$82:$B$89,FALSE)))+ IF(ISNA(INDEX($A$3:$A$10,MATCH($D3,Reference!$D$82:$D$89,FALSE))),0,INDEX($A$3:$A$10,MATCH($D3,Reference!$D$82:$D$89,FALSE)))+ IF(ISNA(INDEX($A$3:$A$10,MATCH($D3,Reference!$F$82:$F$89,FALSE))),0,INDEX($A$3:$A$10,MATCH($D3,Reference!$F$82:$F$89,FALSE)))+ IF(ISNA(INDEX($A$3:$A$10,MATCH($D3,Reference!$H$82:$H$89,FALSE))),0,INDEX($A$3:$A$10,MATCH($D3,Reference!$H$82:$H$89,FALSE))))/Reference!$P$6)* Reference!$O$6
The second type of calculation determines totals for T1 - T4 materials. Since we are dealing with fixed batch sizes, a simple sum doesn't work. We have to calculate everything against batches. Again, this formula is used to sum T3 components based on user entered values (column C) and the carry over amount (column E) =IF(SUM($E3,$C3)>0,IF(MOD(SUM($E3,$C3),Reference!$P$5)>0,SUM($E3,Reference!$P$5),SUM($E3,$C3)),0)
The last formula (and the first one I wrote) is used to determine T0 materials required: =(INDEX($N$3:$N$17,MATCH(INDEX(Reference!$A$22:$A$36,MATCH('Amount - Time'!P3,Reference!$B$22:$B$36, FALSE),1),$L$3:$L$17,FALSE),1)/Reference!$P$3)*Reference!$L$3
Everything else is just cell references to values entered in Reference!A2:A16 and Reference!A22:A89.
I appreciate the paranoia in your post, kind of. Not a big fan of the implication that I coded something malicious.
Now, back to work for me.
|
Makar Kravchenko
|
Posted - 2010.05.01 16:47:00 -
[20]
lol thanks, nice work there..
you could have probably linked to a .TXT
instead of posting the forum :P
i know i know, im such a nitpicker.
|
|
Baneken
Gallente Aseveljet Sev3rance
|
Posted - 2010.05.02 05:42:00 -
[21]
Originally by: Nezz Jaran
Do you get the information you're looking for if you expand cells E, F, I, J, M, and N?
Works now, thanks, haven't used exel for years so I didn't notice that cells had "left out" some alphabets.
http://desusig.crumplecorn.com/sigs.html |
Culmen
Caldari Macabre Votum Morsus Mihi
|
Posted - 2010.05.02 17:48:00 -
[22]
Edited by: Culmen on 02/05/2010 17:47:58 A planetary overview would be nice.
Basically a list of all structure you've built on planet. Which you can right click.
Would be useful to track down that satellite colony you built 2000km from your HQ, with a space port. and further more why do i even need a sig? |
bigsteve
Minmatar STK Scientific The Initiative.
|
Posted - 2010.05.02 19:10:00 -
[23]
Its probably not going to be very easy but How about a building time amount for a selected items, like how much time would it be to build like 6000 Robotics ==-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Who Blew me |
Cadde
Gallente 221st Century Warfare
|
Posted - 2010.05.03 11:58:00 -
[24]
@OP
Nice work with the spreadsheet. It lacks some desired functionality (I grabbed the one before 1.1 though) but other than that it's quite nice.
Originally by: bigsteve Its probably not going to be very easy but How about a building time amount for a selected items, like how much time would it be to build like 6000 Robotics
I am pretty sure someone (read, a whole bunch of people) are making websites with as much time spent on design as on code that will do all the calculations for us.
My opinions belong to me, you can't have them!
|
bigsteve
Minmatar STK Scientific The Initiative.
|
Posted - 2010.05.07 18:44:00 -
[25]
i think the next version should have a way to set-up your planetary building. 1. select CC 2. Add Extractors 3. Add Processors 4. Add Storage 5. Add Extractor outputs. 6. a display of how much is made per hour...
will be quite difficult with T2-T3 items though.... |
Kyra Felann
Gallente Neh'bu Kau Beh'Hude Ushra'Khan
|
Posted - 2010.05.07 21:47:00 -
[26]
Originally by: Makar Kravchenko It would probably be better to post your formulas with example explaining how they work, rather than a link to and .xls
Or just post it on Google Docs and share it.
|
Mythra
Caldari La Mortis
|
Posted - 2010.05.07 22:35:00 -
[27]
I added to my copy a feature that you might want to consider. I wanted to know how much I needed to extract per cycle to make the fuel for a POS, so in AE column I added a simple division formula to calculate minimum extract amount to keep pace. Not skilled enough to add a drop down to let you select your desired production speed but its 4 cycles a hour so that makes 96 extracts a day. Probably only useful for people trying to make production chains that can keep up with their POS fuel needs.
|
Sade Onyx
|
Posted - 2010.05.08 16:25:00 -
[28]
nice spreadsheet, thanks!
Any reason you put those 3 base materials in red? Are they the more rarer ones?
|
Sjolus
Metafarmers MeatSausage EXPRESS
|
Posted - 2010.05.08 16:48:00 -
[29]
Originally by: Sade Onyx nice spreadsheet, thanks!
Any reason you put those 3 base materials in red? Are they the more rarer ones?
Originally by: Nezz Jaran Edited by: Nezz Jaran on 30/04/2010 14:28:04
Originally by: Irillide Is there any significance to Autotrophs, Felsic Magma and Reactive Gas being bolded and coloured red?
They can only be found on a single planet type, so I felt they should stand out a little.
|
Clansworth
|
Posted - 2010.05.08 20:34:00 -
[30]
Looks great! Only comment I have for starters is it'd be nice if the T0 colums were lined up with their T1 counterparts. Alphabetical is nice, but with T0/T1 being a singular relationship, I think paired up makes more sense.
Final BP based productions would be neat, as well, but I haven't looked kinto how many non-planet-goo components are involved in them, which would complicate things.
Intel/Nomad |
|
|
|
|
Pages: [1] 2 :: one page |
First page | Previous page | Next page | Last page |