User Tools

Site Tools


training:guides:industry_spreadsheets

This is an old revision of the document!


Introduction

In this guide, I’ll teach you how to create your own spreadsheet for all your indy needs, anything from T1 frigates to titans and everything in-between. Note that a lot of this you may not understand and I will try to explain everything as simply as I can so both spreadsheet vets and noobs can understand it. This will specifically be for Google sheets as all of my experience is there. I will constantly be referring to a personal spreadsheet with screenshots for a visual explanation as some people learn better that way, I’ll also make a step by step guide for a simple spreadsheet for those that learn by doing. Disclaimer; if it feels as if I’m talking to you like a child, I apologise, I would just like to make everything as clear as I can so everyone is able to do it and learn.

Setting up your spreadsheet

Firstly you will want to open up a brand new google sheet with a plan of what you want to make. You can do this by going to the Google homepage and in the top right corner there are a load of dots, click that and then click on drive as shown below; Note that you will need a Gmail to be able to do this.

This will bring up your drive, from here you can simply click new and select google sheet in the top left. You can start by giving you spreadsheet a name if you wish by double-clicking the current name.

Creating a data pool

Now that you have a spreadsheet set up and your ready to start making it you're going to want to create a data pool. What I mean by a data pool is something that everything else can refer to when you create your formula’s, for an example the minerals each ore gives or the jita price of each mineral as shown here.

What you do with this data and how you manipulate it determines how easy a spreadsheet is to use or look at and what the outcome will be. As you can see I have the price for each mineral here, and the way I manipulate this will, in turn, show the mineral price of each ship I can build or in other words how much it would cost for me to build the ship if I were to buy the minerals. This can be further manipulated by adding more data.

Pulling data

To pull the data as shown above I use a simple tool from evepraisal.com that I will link here. You simply install it and you can use it as an add on in your google sheets; you can turn this on by going to Add-ons at the top of the sheet and clicking manage add-ons, this will pull up a box and you should see the add-on there, click manage and then ‘use in this document’ as shown below.

This adds a simple formula that will save you hours of frustration and hair pulling and allow you to pull data from evepraisal.com directly, checking the price of anything you want in your spreadsheet… given it’s spelt correctly, the formula is as shown =EVEPRAISAL_ITEM(A1), The cell in the ‘( )’ will hold the name of the item you want the jita price for. For example =EVEPRAISAL_ITEM(A25), and rather than the formula you see here the data you have requested will be shown in the cell you put this in, like so.

Note that pulling too much data from an external source like this will cause unnecessary load times and lag in your spreadsheet, so try to pull only what you need to try not to pull the same thing more than once. You are able to refer back to the same pull as many times as you need.

Finding the mineral price

So to start with we will make a spreadsheet to tell us what the mineral price of a Tristan is and from this determine if it is worth building. So if we were to have a Tristan blueprint copy (BPC) with a 10 material efficiency (ME) it would take the following minerals to build:

Titanium - 20790
Pyerite - 5643
Mexallon - 2673
Isogen - 297
Nocxium - 70
Zydrine - 20
Magacyte - 2

With this data, we can’t really do much because we don’t have a value for the minerals. So we will use the ones in the picture below and I will show them below like so:

Titanium - 4.81
Pyerite - 3.87
Mexallon - 63.76
Isogen - 16.46
Nocxium - 306.57
Zydrine - 495.83
Magacyte - 477.99

So now that we have a data pool we need to create our mineral value we can go ahead and insert the formulas needed, this formula is pretty simple as you are just multiplying the mineral amount by the mineral price using =A1*B2 as an example.

As you see here I have inserted the data and formula, telling it I want the required tritanium multiplied by its price, at this point you simply press enter and it will show the value.

Now that you have your first formula all set up you can copy that for each row all the way down to megacyte, but instead of typing it in over and over you can instead click and drag the little square in the bottom left of the cell to where you need it, in this case, down 7 cells.

As you see that copies that formula to the other cells and can make formatting and entering formulas so much faster. So now that we have found the price for the quantity of each mineral we need to add the prices together; this requires a different formula called SUM and it goes like this =SUM (G2: G8) and I will add this to my spreadsheet show you below.

As you can see we now have a total price for all the minerals needed to build the Tristan but no price to compare it with so we need to find the price of the Tristan using the =EVEPRAISAL_ITEM(A1) again.

Now that we have done that we can now see the price of the Tristan and the mineral value of the Tristan But it does not look too great, so we need to do some spreadsheet management and data forwarding taking us onto our next section.

training/guides/industry_spreadsheets.1556391672.txt.gz · Last modified: 2019/04/27 19:01 by Sidoen