Zeros in spreadsheets are ugly - Nested IF() Statements

So I'm sure you're all aware of the formula's in my existing and previous spreadsheets has featured a lot of nested IF() statements.  The reason for them is to hide zero's where ever they may cause eye strain.

I hate zeros on a spreadsheet.

They are not needed, they tell you nothing a blank cell can't.

Here's a simple statement to hide a zero.

If(A1=0,"","FOOD!!")
This will bring a blank cell if the named cell A1 has a zero in it, or is blank, otherwise it will print the word "FOOD!!" where ever you type the formulae.  It didn't have to be blank of course, you could add "Get Some Food" between the quotation marks and it would tell you to do that instead, so that's a simple one.  Things have gotten more interesting lately, take a look at the EIO: Refinery sheet for some extreme examples.

So I've been busy today focusing on the Time and Building formulae both for Raw and Extra materials, and now I've arrived at the stage where I need to add up the possible two figures.

But.

You can't add text and a number together to get a numerical result.  It's just not happening.

That drives even more nested if() statements.

Here's the flow chart I'm about to use to help follow the logic path.  No apologies made about my handwriting skills, it's a sketch not a calligraphy art project.

Believe it or not, I've never tried this method before so I hope it works.  T = True, F=False.

edit: Works like a charm!

Public Test Version

I have realised I would like to test what files will be required and what I can do to keep the links between the spreadsheet and the database intact.

Please download the file from http://eve-files.com/dl/259341 It contains three files, the evedb MS Access database file and the EIO: Refinery Excel Spreadsheet and finally the MarketPrices.xml file obtained from Eve HQ.

Please let me know what if any errors or problems you receive and I will do my best to provide a solution.

Contents Sheet

This page lists the sheets, what they're supposed to do, named ranges used within the sheet and so on.  It was a quick reference guide for myself so I could keep track of what was where and why.  Any hidden rows or columns is for the user benefit, either because they contain a column reference number or because they're blank.  It probably isn't a good idea to edit or delete these.

Eve HQ Usage

In previous versions I pulled market data from eve-central and updated different xml ranges.  This was a tedious and nightmarish process which I'm very glad I no longer have to do because of the local saving of the prices download provided by Eve HQ.

To download Market Prices from a particular region, complete the following steps after pulling your own character details.  This isn't necessary but makes the application look a lot nicer than a blank screen.

1. Start EveHQ.  On the View section of the ribbon, click the "Prices System" icon.
2. The Market Price screen has four tabs, click the Price Groups and tick the region you would like to select the prices for.  Above this section is the available columns.  I have eight selected although EIO Refinery uses only two, Median Price (Buy) and Median Price (Sell), however changing column count may cause import errors and EIO to report the incorrect value.  You can select as many regions as you like but EIO will only use one.
3. Go to the Market Prices tab, and hit the Download Market Prices button.  Wait for the process to complete after it Parses the regional data, both buttons are disabled while this process is running.
4. Congratulations you've made Eve HQ a lot more useful.
5. On the Ribbon bar in the API Tools section, select "Open APPData Folder". This will open the application data folder for Eve HQ.  You should have a folder called "MarketCache" and inside there you will find an xml document and a WinRar file for the region(s) you just downloaded the prices for.  Copy the xml file and paste it in to the same folder, rename it to "MarketPrices".  Cut and paste in to the location you saved the EIO:Refinery and Paste it there.
6. Open Evedb Access and open the query titled "Materials".  This may take a while.  Close Access.
7. You can now Refresh the "Materials" Table via the Design tab in the External Table Data.  Again this may take a while, the more cores the better.
8. You've completed the manual price update.  For best results, check the market before you buy or sell anything and enter totally up to date values on the materials important to you.
9. Don't forget to save the file or you're prices will be out of date again.
10. The price updating shouldn't need to be done more than once a day, just update the prices from EVE HQ every so often and rely on your own prices as these will ALWAYS be used first where present.

If you don't use this awesome programme you're missing out.  Download it from http://www.evehq.net/

Coming Soon

I will leave you with the enticing promise of more spreadsheet screen shots detailing my thought process on how to get the best results from EIO:Refinery so it works for you.  Perhaps combined screenshots will be easier on the eyes.