Leather Conversions Spreadsheet & Bags

Leather Conversion

I promised you more spreadsheets so today we have a Leatherworker's spreadsheet. I wrote a post way back near the beginning of Cataclysm about converting leather scraps up to better qualities as a way to make some easy gold. It's nice to see that the old tips & tricks still work now that Mists of Pandaria is live & I've included some of the older conversions in this spreadsheet as there is still gold to be made there.

Now there are two ways to use this spreadsheet - you can download a copy of my Leather Conversion spreadsheet & just manually input prices from your Auction House or you can use The Undermine Journal .csv file as I explained in last weeks post Easy Data from TUJ.

If you decide to manually input the prices, you will need to overwrite the VLookup formulas in column B but the rest of the spreadsheet will still work. (If you want to try your own VLookup formulas, keep reading, instructions at end of post)

If you want to try the TUJ data file, follow the instructions in the post to get a .csv file onto your PC. Open it in Excel or Open Office & just copy/paste the whole sheet into tab 2 of my Leather Conversion spreadsheet. It should pull the data through to the main sheet automatically.

Leather Conversions Spreadsheet

So what are we looking at? Well, I've tried to keep it fairly simple but it took me a while to get there so I hope you can follow what I've done! Converting up from the various leather scraps is a bit more time-consuming but can be very profitable - just look at the price of Savage Leather Scraps on my server today (red dot line). Converting up from those scraps means that my Savage Leather would only cost me 33 silver - a big saving over the 9 gold or more that someone is trying to sell their Savage Leather at! If there are enough Savage Leather Scraps to convert to a Pristine Hide (250 scraps, in case you were wondering), that's only 17 gold 50 silver for a Pristine Hide - big profit there or even use the ultra cheap hide to make the scribe or mining bags for even more profits!

Now I've only included the bags here but you can easily add rows for leg armours or anything else you like - add a row for the leg armor & a row for each type of material it uses besides the leather then use a VLookup formula to get the prices from the TUJ data file. A quick a-b formula later & you should see if it would be profitable or not.

Ok so I promised help with the VLookup formula for those of you not sure about spreadsheets. It's really very easy but sometimes the help files make it confusing. This is what Open Office says :-

"Syntax

=VLOOKUP(SearchCriterion; Array; Index; SortOrder)

SearchCriterion is the value searched for in the first column of the array.

Array is the reference, which is to comprise at least two columns.

Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.

SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available."

OK - so the Search Criterion is what you are looking for in that huge table of data - in our case Savage leather, Pristine Hide etc. For this formula to work, your description in the main spreadsheet must be identical to that in the data file so watch for spelling mistakes & random apostrophes.

The Array part of the formula is merely the parts of the huge data file that the formula is going to look at. The formula doesn't need to look at the whole file, it always starts with the item name column so you have to tell the formula which columns to look at. In our case, it only needs columns C & D so I click C1, hold down the shift button & page down to D12000+.

The Index part of the formula is which column contains the data you want to use in the formula - for us that would be column D or 2 (it's the 2nd column in our array). If we wanted to pull data from other columns, we would just increase the Array columns (say columns C to H) & put a 3,4,5 or 6 in the formula to pull data from that column.

Sort Order is important to us here - the data file is so big, we really don't want to sort it in ascending order before we do anything so the last part of the Vlookup formula for us is a 0 (zero).

So for this file, our Vlookup formula looks like this 

=VLOOKUP(A5;'TUJ Data'.$C$2:$D$12668;2;0)/10000

The Undermine Journal data prices are expressed in coppers so I have divided the Vlookup results by 10,000 to get to a gold/silver price.

So there we go! I hope it's clearer than mud to those of you who need the help but if you are having problems, do feel free to comment, tweet me or email me & I will try to help further. 

As for leather converting - don't forget, you can convert the lower leathers up to higher ones too - it's rarely profitable but if you just need that 1 or 2 more pieces to get a skill point, it can be a time saver.

Image © Austin Appraiser under Creative Commons licence