User Tools
training:guides:industry_spreadsheets
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
training:guides:industry_spreadsheets [2019/04/27 19:07] Sidoen |
training:guides:industry_spreadsheets [2019/04/27 19:10] (current) Sidoen [Pulling data] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Introduction ====== | + | ====== 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. | 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 ====== | + | ===== 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. | 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. | ||
| Line 11: | Line 13: | ||
| 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. | 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 ====== | + | ===== 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. | 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. | ||
| Line 19: | Line 21: | ||
| 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. | 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 ====== | + | ===== 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. | 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&600|}} | + | {{: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), | 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), | ||
| Line 32: | Line 34: | ||
| 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. | 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 ====== | + | ===== 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: | 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: | ||
| Line 76: | Line 78: | ||
| 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. | 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 ====== | + | ===== 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. | 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. | ||
| Line 88: | Line 90: | ||
| 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. | 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 ====== | + | ===== 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. | 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. | ||
| Line 100: | Line 102: | ||
| 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. | 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 ====== | + | ===== 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. | 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. | ||
training/guides/industry_spreadsheets.1556392049.txt.gz · Last modified: 2019/04/27 19:07 by Sidoen