open All Channels
seplocked EVE Technology Lab
blankseplocked [Spreadsheet Calculator] TAURI Mineral Calculator
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

Aidan Patrick
Aldebaran Foundation
Tauri Federation
Posted - 2011.04.16 15:36:00 - [1]
 

Edited by: Aidan Patrick on 30/04/2011 10:08:46
Ok, so I recently updated my spreadsheet, and I'm also updating the post to be a little bit more thorough and not so TL;DR.

What is this?
This is a spreadsheet calculator for mineral/resource yields on Ore, Ice and Drone Poo. You type in the quantities that you have, it figures out how many batches you have to refine and gives you the mineral yield as well as some other semi-useful data. (leftover units, volume etc)

In addition to the mineral actual yield calculator it has a built in profit calculator (You need to enter the prices yourself) and a third calculator that is for the system I'm using for my corporation when splitting everyones cut from a group mining op. (More on that later if you're interested).

The Files
Preview it with random data pre-entered.
Download it in .xlsx format (contained within a .zip) from EVEFiles.
Copy it from Google Docs.

Mineral Yield Calculator (MYC) Sheet
This sheet is extremely simple. You're only supposed to edit the light gray fields for each of the ore/drone/ice quantities. Manually editing the other fields will screw up all the other formulas. The calculations for each yield pull from the Yield Reference Chart sheet.

Yield Reference Chart Sheet
This sheet shows the information on the volume (m3/unit) of each ore/ice/poo, as well as the batch, and the volume of an entire batch. On top of that it also shows what products the source refines into. Should there be a patch changing the yields, m3, or batch size, editing this sheet will suffice for updating the entire spreadsheet. With that said however, errors on this sheet will screw everything up.

Mineral Value Calculator (MVC) Sheet
This sheet is pretty straight forward as well. Light gray fields are all you should edit. The top table is for entering the ISK/Unit price for all the refined goods, which once done the sheet will auto-calculate the value of the refined product from the MYC on an individual level, and give you the total value.

The Cut Calculator below is also pretty straight forward, just enter a custom name (if desired) on any of the cuts to track what it is, then enter a plain number (no decimals required) for the percentage (IE 15) and it will auto-calculate that cut from the combined mineral value. A breakdown of the cut information is available at the bottom of this table.

Participation Point Calc. (PPC) Sheet
This sheet is not required. It is used for the method in which I divy up isk/mineral cuts at the end of an op.
ISK calculations on this sheet are calculated based on the remaining value from the MVC sheet to enable you to take out cuts (such as a corp tax) out before using the PP System. If you plan to divy out minerals instead of ISK however, please note the sheet does not account for pre-removed cuts from the MVC sheet.

The PPC Sheet uses my Participation Point method to distribute either an ISK or mineral cut post-op (more on this in a separate post). For tracking purposes you enter each persons name and their ship. You then enter the PPR for that ship, and the times they started, and stopped participating in the op. It will then show you the PPE (Participation Points Earned) and calculate their cut via ISK. Additionally if you are going to split the minerals at the end instead, there is a column for each mineral (including for the resources yielded by ice) that shows you how much of each material they are to receive.

At the bottom of this sheet there is some data that shows how much ISK/Minerals you yielded total, as well as showing how many (if any) units are left over after giving everyone a cut.

Aidan Patrick
Aldebaran Foundation
Tauri Federation
Posted - 2011.04.16 17:43:00 - [2]
 

Edited by: Aidan Patrick on 30/04/2011 10:35:34
This post is only relevant if you plan to use my Participation Point system calculator that is contained within the spreadsheet. If you aren't interested in that portion, don't bother reading this.

Participation Points - The Concept
The idea behind the PP System (teehee!) is quite simple. My goal with the system is to enable the person running a mining op to spread out everyones cut fairly based on what they provided instead of just splitting the entire pot evenly between everyone who participated.

Splitting the pot evenly is great, sorta. What happens when you do a complete even split is newer players say mining in frigates for example get more ISK out of the op than they contributed to making; while Hulk Pilots for example, get less ISK than they contributed because individuals providing less are getting a larger cut than they should be.

In a nutshell, the idea is simple. You produce big, you get big. You produce small, you get small.

Where do the points come in?
The system relies on what I call the PPR (Participation Point Rate) which is how many points you get per hour. This number ranges from 0.00 to 1.0. A hulk pilot for instance will receive 1.0 PP/hr. As an example a Retriever pilot receives 0.5 PP/hr.

Now lets say these two pilots mine together for a full 4 hours. The hulk pilot has 4 PP's (participation points), while the Retriever pilot winds up with 2 PP's. Lets say they made 100m ISK worth of ore for the sake of an example.

Together the pilots earned 6 PP's. We now divide 100m by the total amount of PP's earned by the pilots in the op. 100m/6=16666666.66666667.

This means that for every 1 PP as a pilot has, they will get $16,666,666.66 ISK. With that said, our hulk pilot will get 4x$16,666,666.66=$66,666,666.64 while our retriever pilot gets 2x$16,666,666.66=$33,333,333.32.

When you combine the two values you wind up with $99,999,999.96 (The spreadsheet accounts for all decimals, so you won't be .04 ISK off with it like I am here without it)

How do you track the data?
Tracking of the data is very simple if you use the spreadsheet. Basically the person running the op enters each participants name and ship (name so you know who to give the cut to, and ship for reference). Then you enter the times that they started and stopped participating

I recommend using 24hr EVE time as I know it works. Additionally, don't trust the spreadsheet if you cross the 24hr and you're using google docs as its time calculations can be wonky in that case. If you are using excel, only trust time calculations that cross the 24hr/12hr mark if you are using the Ctrl+Shift+: hotkey to enter the time information.

So I entered the data...
Once you've entered the data for the start/stop/ship/player you need to enter their PPR. This is will be a number between 0.00 and 1.00. I use a hulk for 1.0 as it is fields the best M3/hour in a standard ore mining op. I calculate the PPR rate for other ships based on what percentage of the hulks m3/hr they mine. So say I have a BS that mines at 50% of the rate of the hulk, that ship gets 0.50 PPR.

Once I get my PPR chart created I'll also release that information in this thread.

Conclusion
I don't know what other information I should mention on this right now, but if you have questions just post here or evemail me and I'll be happy to answer any questions. Feedback is also very welcomed.

Sidrat Flush
Caldari
Eve Industrial Corp
Posted - 2011.04.17 08:05:00 - [3]
 

Nice layout.

Could you list your formula for the refine tax, I may implement it instead of using the perfect numbers. Hopefully ingame rounds to 2 decimal places and not 10.

Aidan Patrick
Aldebaran Foundation
Tauri Federation
Posted - 2011.04.17 13:22:00 - [4]
 

Edited by: Aidan Patrick on 17/04/2011 13:26:21
Edited by: Aidan Patrick on 17/04/2011 13:23:47
Thanks!

The formula I use for the tax rate is this:

=SUM(('Yield Reference'!C2*G4)*I4)

The way I have the formula working is it pulls the batch yield from the second page of the spreadsheet for that specific mineral (so they can be changed across the board easily should an update change mineral yields). Then it multiplies that amount by the G column (contains: =SUM(E4-F4) ) which I have hidden. The G Column contains the sum of Q2 (manually entered tax rate, you can enter it with as many decimals as needed) and the E column which is a manually entered refine rate. Eventually I'll likely program in the refining skill levels some how, but for now you have to manually adjust the refine % on each ore type, same thing here - you can use as many decimals as needed.

Once the calculation in the formula is ran on the per-batch yield, I multiply the resulting value by the I column, which is the resulting calculation of how many actual refinable batches you have.

Edit: I updated the coloring on the Refine % field so it's more noticeably a user-editable field.

Sidrat Flush
Caldari
Eve Industrial Corp
Posted - 2011.04.17 14:35:00 - [5]
 

Edited by: Sidrat Flush on 17/04/2011 14:37:55
Thanks for the reply. Although very useful to be able to enter your skills and standings I have to say it's usually better to put Ore on the market at mineral prices than refining for less than perfect amounts.

But I reckon that's just me.

You know what would be useful for your sheet though.

A mineral to Ore calculator. Reverse it in other words, e.g you need 1000 trit and 2000 pyerite - what do you mine in the least number of batches that you can access (bad example of course given that the minerals are ultra common).

My sheet had it, wonder what you'd come up with.

Good luck and have fun.

Edit :

Did you type the data values manually! Ouch.

Aidan Patrick
Aldebaran Foundation
Tauri Federation
Posted - 2011.04.17 16:18:00 - [6]
 

I'm not into production yet so I don't have target intake for specific minerals... however I eventually will be. Thanks for the idea - It'll take me a bit to figure out a good way to implement it though as I'm far from pro at Excel.. but I like the idea.

and yeah I agree on the refining. If I wasn't able to get it done perfectly I wouldn't bother with it myself.

Sidrat Flush
Caldari
Eve Industrial Corp
Posted - 2011.04.19 16:08:00 - [7]
 

Originally by: Aidan Patrick
I'm not into production yet so I don't have target intake for specific minerals... however I eventually will be. Thanks for the idea - It'll take me a bit to figure out a good way to implement it though as I'm far from pro at Excel.. but I like the idea.

and yeah I agree on the refining. If I wasn't able to get it done perfectly I wouldn't bother with it myself.


I believe you can find an example on the Tyrannis version of the Eve Industrial Organiser available in the link below. I can't remember exactly what I did to get the information but you should be able to follow it quite easily from the formula.

http://dl.eve-files.com/media/corp/Sidrat/EIO_Tyrannis.zip

Aidan Patrick
Aldebaran Foundation
Tauri Federation
Posted - 2011.04.19 23:31:00 - [8]
 

Thanks Sid! I'll check it out.

Aidan Patrick
Aldebaran Foundation
Tauri Federation
Posted - 2011.04.30 10:38:00 - [9]
 

Edited by: Aidan Patrick on 30/04/2011 10:52:31
Edited by: Aidan Patrick on 30/04/2011 10:38:58
Changelog & Bump
  1. Spreadhseet Updated

  2. Thread name Updated

  3. Information on my Participation Point system added to explain what the 4th sheet is used for.

  4. EVE FIles Download link added

  5. Initial post made easier for the TD;LR


@Sidrat
Finally took a look at the spreadsheets in that zip file since i just installed office last night. Very detailed sheets. Can't say I'm a big fan of the overall design lol, but the data is great. I'm going to see about maybe implementing some of the features you've got into mine.

Also, it looks like your token system is very similar to my PP system; glad I'm not the only one with a similar mindset on distributing peoples cuts; means my PP System should be easier for people to cope with.


 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only