I was going to use 3 different spreadsheets:
Collection
Trade
Foil
But I was able to design a way to keep all the data for a specific set on a single sheet. This way I will not have to use multiple spreadsheets in order to keep everything straight. I know, the Price spreadsheet is a separate sheet, but we will get to that later.
So for the basic setup of the Collection spreadsheet, there are different types of Tabs. There are Collection Tabs, and there are Set Tabs. The Set Tabs are for a single set that was released by WotC. The example we are looking at today is from the Tenth Edition Set Tab.
Here is what the set Tab looks like for the Tenth Edition. I only have to top part of this Tab as to explain what was done.
Collection Section
Trade Section
Foil Section
Card Count Totals for Collection, Trade, Foil, we will talk about this in a future Blog
_________________________________________
Sheet Totals
In the Sheet Total I have them into two different totals for verification.
Total 1: =SUM(SUM(A7:A12)+SUM(A17:A22)+SUM(A27:A32))
Total 2: =A6+A16+A26
Total 1 will take all of the Rows in a Section and add them together
Total 2 will take the Section Totals and add them together
_________________________________________
Section Totals
Sections are labeled with the name in Blue
Collection
Trade
Foil
Directly under the section title is the Section Total.
Section Total: =SUMIF(H2:H602,">0",H2:H602)
Here we are going to be using a SUMIF command. This will allow us to look at column, match an expression, and then SUM a total if the expression matches. So in this instance for the Section Total we will be looking at the 'H' column, checking to see if the value is greater than 0 (>0), and if the cell is greater than 0 we will add all those values up together.
=SUMIF
Excel command we are using
H2:H602
Column we are looking in
">0"
Value we are looking for
H2:H602
Column we will be adding together if the values match
Now for a little background as to why I had to do this rather than just SUM the whole column. From the Price.xlsx we are pulling from TCGPlayer.com. For some of the cards the price is set to 'SOON', and that just means the site does not have a price for that specific card yet. Now, we will get to the card pricing in a later blog, but I had an issue when trying to calculate the prices of the cards when there was a 'SOON' value for the specific card. So with this new formula, if there was issue and the value was something other than a price, this formula will skip over the cell values that are not greater than 0.
Now, we will be losing that value in the totals we are looking for, but we will at least be able to get the totals on the cards there are prices for. If we did not do this, we could get 'VALUE' in some of the totals instead, and that is a pain in the ass to try and sort out. So in losing some of the prices due to TCGPlayer not having a price, we still get a running total for all the cards they do have a price for.
_________________________________________
Card Types
Under each section there are rows for each card type known today:
M: Mythic Rare
R: Rare
U: Uncommon
C: Common
L: Land
T: Token
Each Card type also has a total for the section it is in:
Collection
M: =SUMIF(I2:I602,"M",H2:H602)
R: =SUMIF(I2:I602,"R",H2:H602)
U: =SUMIF(I2:I602,"U",H2:H602)
C: =SUMIF(I2:I602,"C",H2:H602)
L: =SUMIF(I2:I602,"L",H2:H602)
T: =SUMIF(I2:I602,"T",H2:H602)
One thing to point out, the Card Type Total will only pull from the Columns for that specific Section. So for the Collection totals, we will only be looking at columns 'I' and 'H'. This way we can get specific data on the Card Type totals, and keep track of each Card Type easily. In a previous spreadsheet I manually '+' each of the cells into a single total for each Card Type, and it was a huge pain in the ass to setup.
=SUMIF(I2:I602,"M",H2:H602)
Again if we look at this formula we are using a SUMIF command. This will allow us to look in one column, check to see if there is a Card Type we are looking for, and then add all those cells together.
=SUMIF
This command will allow you to do a lookup
I2:I602
Here is the section that you are looking at
"M"
This is the character you are doing the lookup on
H2:H602
This is the section you are going to be adding up if there is a "M"
So for each Card Type we are looking into the 'I' column, looking for that specific Card Type and only adding up the prices for those cards in column 'H'.
_________________________________________
Verification
So like I mentioned before we have two different totals we are looking at.
Total 1: =SUM(SUM(A7:A12)+SUM(A17:A22)+SUM(A27:A32))
Total 2: =A6+A16+A26
Total 1 is looking at the SUMs of each Card Type in each Section
- Each Card Type is looking at column 'I' looking at the Card Type, and then adding up all the Card Types that match.
Total 2 is doing a SUM of each Section Total
- Section Totals are just adding up all the prices that are greater than 0 in a specific column (Example: Column H).
This way we are looking to see if we get the same total two different ways. Now, you are most likely asking yourself why in the world would I do something like this? Well with different version of my spreadsheets, I have always found that I mess up a lot and the totals you get are not real in some cases. Now I have been able to combat this with using the SUMIF statements, but it is generally a good practice to see if you get the same outcome using multiple formulas.
So you are getting a total of all the prices in a single column, and you are getting the prices of each Card Type and adding those together to see if it is the same value. I know, it might be overkill, but I really like knowing that my data is correct, and I am looking at the true value of my collection.
_________________________________________
So now that we have gone through that, I will go through the rest of the design of the Set Tabs in a later Blog.
No comments:
Post a Comment