These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Science & Industry

 
  • Topic is locked indefinitely.
 

spreadsheet nerds join in on the fun
Author
Previous Topic Next Topic
Jason McCoy
Deep Core Mining Inc.
Caldari State
#1 - 2012-02-19 01:25:41 UTC  |  Edited by: Jason McCoy
Surprisingly mining seems like its a viable form of income again.

Either way, I have a spreadsheet built (surprise surprise!) the sheet has two areas for user input.
column A: |Mining op start| column B is the number in eve time |06:00:00| column C |mining op end| column D |07:30:00| column E is |total mining time| column F |01:30:00| I am calculating this number horizontally across columns instead of using rows.

By looking at the data we know the op started at 06:30:00 ended at 07:30:00 and the total op time was 01:30:00 which is actually 1.5 hours of mining time.

How the heck can I get this number? The actual mining time; it is hard to explain what I want. 1.5 hours is not the same as 01:50:00

I need this number in order to calculate isk per hour with the mining op. The sheet already calculates refined minerals from ores mined. Example: I input the amount of plagioclase mined, the sheet gives me the amount of minerals from refining (skills already accounted for). In another section of the sheet I can update market prices and it calculates everything down for iskies.

This sheet is an awesome tool to make actual isk payouts on the spot to corp members.
But in order to compare, I really want to calculate isk per hour within the sheet.

am I clear with what im asking about? if not please ask so I can clarify and see if I can get this sheet perfected a bit more.
Alyssa SaintCroix
Leihkasse Stammheim
#2 - 2012-02-19 01:35:19 UTC
This is completely random, but I've noticed the price of Trit slowly going up and I've even heard that there were days when it was above 7+ isk per unit.
Jason McCoy
Deep Core Mining Inc.
Caldari State
#3 - 2012-02-19 02:38:20 UTC  |  Edited by: Jason McCoy
Yeah while I was pewing in 0.0 I decided to undock an orca and a pair of hulks in high sec and mined for around 50 minutes. After updating the spreadsheet with market prices I was surprised to see I had 26M in minerals. Not bad for 50 minutes of mining.

This is why I would love to program the sheet to calculate the isk / hour.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#4 - 2012-02-19 03:18:34 UTC  |  Edited by: Steve Ronuken
As long as you format the columns as times, you should be able to subtract one from the other and get the proper time in days.

So if you multiply that by 24, you'll get the number of hours.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jacob Lyon Chieve
Sanguis Mortem Industrial
#5 - 2012-02-19 03:29:05 UTC
Also, be aware that when inputting the times, you need to literally input the time as it is formatted.
DO: 01:00:00 for one hour
DON'T: 1.0 for one hour

All times are actually calculated as portions of 24 hours. In your example, 1.5 hours is actually 0.0625 of a day. Hence, multiplying it by 24 will get you the proper number with which to calculate your values from.

HTH

Need to know how much ore to mine in order to yield a certain order of minerals? https://forums.eveonline.com/default.aspx?g=posts&t=67477 It will even calculate how long you can expect for it to take!
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#6 - 2012-02-19 03:34:52 UTC
Excel (2010 probably other versions) handled 6:30 fine, for half past 6.



Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jacob Lyon Chieve
Sanguis Mortem Industrial
#7 - 2012-02-19 04:16:37 UTC
Right, as does google docs - I just got into the habit.

Also, use the ##:##:## format, as the ##:## only format ends up with wierd calculations when you roll into another day's time frame.

Need to know how much ore to mine in order to yield a certain order of minerals? https://forums.eveonline.com/default.aspx?g=posts&t=67477 It will even calculate how long you can expect for it to take!
Jason McCoy
Deep Core Mining Inc.
Caldari State
#8 - 2012-02-19 04:23:38 UTC
Jacob Lyon Chieve wrote:
Also, be aware that when inputting the times, you need to literally input the time as it is formatted.
DO: 01:00:00 for one hour
DON'T: 1.0 for one hour

All times are actually calculated as portions of 24 hours. In your example, 1.5 hours is actually 0.0625 of a day. Hence, multiplying it by 24 will get you the proper number with which to calculate your values from.

HTH

I'm going to try this. The document is built using google documents because I want it to be as public as possible, for all fleet members to view at their leisure.

edit:
its what ive been doing, logging all times as 06:25:00 just for an example.
Jason McCoy
Deep Core Mining Inc.
Caldari State
#9 - 2012-02-19 04:29:35 UTC  |  Edited by: Jason McCoy
OK so I did the cell * 24 and it gives me the number, now im taking that number and dividing by total isk made per op and its giving me an output of $0.00

For example:
start time 06:00:00
finish time 07:00:00
total time is 01:00:00
number 1 - this is the number I get by multiplying the total time * 24
I take the number 1 and divide it by the hypothetical amount of 26M isk and im getting zero.

what the heck am I doing wrong?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#10 - 2012-02-19 05:05:40 UTC
if you want the ISK/hour

Assuming that the difference of time is in A3, and the amount earnt is in B1

The formula
=B1/(A3*24)
Will give ISK/Hour


https://docs.google.com/spreadsheet/ccc?key=0As88qKwn3d59dFZCeS1JblR3VlFLNkpPSnpMTTZZc2c

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jason McCoy
Deep Core Mining Inc.
Caldari State
#11 - 2012-02-20 01:27:44 UTC
Wow I thought I had replied last night, I switched the values around and it works like a charm.

The key was the * 24

thanks gents