Test Wiki Please Ignore

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Spreadsheets Online ======

===== 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.

{{:training:guides:spreadsheets_online_001.png?nolink&400}}

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.

{{:training:guides:spreadsheets_online_002.png?nolink&400}}

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.

{{:training:guides:spreadsheets_online_003.png?nolink&800|}}

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. 

{{:training:guides:spreadsheets_online_004.png?nolink&400|}}

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.

{{:training:guides:spreadsheets_online_005.png?nolink&600|}}

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.

{{:training:guides:spreadsheets_online_006.png?nolink&600|}}

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.

{{:training:guides:spreadsheets_online_007.png?nolink&600|}}

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.

{{:training:guides:spreadsheets_online_008.png?nolink&600|}}

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.

{{:training:guides:spreadsheets_online_009.png?nolink&600|}}

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.

===== Data forwarding =====

So now that we have a data pool and have manipulated it in a way that we want we now need to make it easier to look at and read, I would usually use an overview tab for this; you can create a new tab in your spreadsheet at the bottom left by clicking on the + button shown here.

{{:training:guides:spreadsheets_online_010.png?nolink&400|}}

Once you create a new tab you are able to pull data there from other tabs as shown below.

{{:training:guides:spreadsheets_online_011.png?nolink&600|}}

This will pull the mineral price from the previous tab onto the current tab as is done by starting the formula with = the going into the previous tab and clicking on the cell you want to be shown on the new tab.   that you can now just delete the old Tristan price pull and do a new one here.

===== Important Formulas =====

So I’ve given the basics of how to make a spreadsheet from pulling/creating data pools to using basic formulas and data forwarding and this will generally be enough to create most spreadsheets that you need or want. There there are some noteworthy formulas to know, one of the main ones is as follows = $K$4 * (1 + B6*0.02) and is used here.

{{:training:guides:spreadsheets_online_012.png?nolink&600|}}

What this is basically dose is anything preceded by $ will not change unless manually changed, so you are able to drag that cell around with the little square in the bottom right of the cell as shown before but it will never change K4, this is useful for multiple formulas used from the same cell.

Another is ! this will allow you to pull from different tabs as shown previously and is useful for collecting all your data into a single tab for ease of use instead of having to search through multiple tabs.

There are a few more and are generally not needed for a spreadsheet like that but is meant for more complicated formula and data manipulation. A few to name are =if and =ifs, these allow you to say if it is the then show x, if it is that then show y and I would recommend researching if needed.

===== Resources =====

I will add a few more ships the spreadsheet and link it here as well as do some data clean up as we discussed before as well as some formula compression to make it look a little nicer as an example then link it here. I have also put a =IF statement in there in the overview tab for those that are curious as it makes it easier to glance at.

Spreadsheet - \\ [[https://docs.google.com/spreadsheets/d/1Mrwq78_jgxgitdSjv9vUCcUFJXLrIrakMVrApZBMBaY/edit?usp=sharing]]

Evepraisal.com add on - \\ [[https://chrome.google.com/webstore/detail/evepraisal-tools/lklblimoabndhjineeemkldkjllinmlj]]
Test Wiki Please Ignore

User Tools

Site Tools

Page Tools
