I realize that this is NOT for everyone, and probably not for most people, but I've now been tracking what I wear everyday for over a year, and it's been great for me. So here is a post explaining how you can set up an excel spreadsheet to track what you wear each day, and have the spreadsheet do various calculations for you.
I hope that some people find it helpful, and feel free to message with your email if you would like for me to email you a template.
I use Microsoft Excel, I'm sure that there are other programs that would work too, but this is all using Excel. I'll start with the most basic, and explain more complex (and less necessary) options as I go. I’m going to focus on clothes in this write-up, but the same works for accessories, shoes, jewelry, etc.
The absolute basics (for anyone not familiar with spreadsheets):
- Rows are numbered (1, 2, 3…) and columns are lettered (a, b, c). If you aren't clear on rows and columns, this visual should help: http://en.wikipedia.org/wiki/Column_(database)
- You can format different cells within the same spreadsheet to contain different types of information (numbers, dates, words). Right click on the cell(s) you want and select format cells to change the format.
The basics:
- You will create columns for the different types of information (type of item, color, cost per wear, dates worn, etc). I will provide a more detailed description of each type of information below.
- You will create rows for each item of clothing.
- I have found it easiest to create a separate sheet within the same document for each type of item (clothes, accessories, shoes, jewelry). You can switch sheets and make more using the tabs (saying Sheet 1, Sheet 2, Sheet 3) at the bottom of the spreadsheet.
Simple column setup:
A = general type of clothing. I have mine separated into bottoms, dresses, layers, and tops.
B = item. For example, within the category of bottoms, I have skinnies, slacks, jeans, skirts, shorts, bermudas. Do what makes sense to you.
C = color.
D = detail. Here’s where I give details about the item. It could be the name of the style (“modern skinny”), general style (pencil), pattern (spotted), fabric (corduroy), or details (ruffles). Write whatever you need to know which item you’re talking about.
E = year purchased.
F = store purchased from.
G = cost. (format the cell to show the number in dollars)
H = cost per wear. This will be calculated by excel, I’ll discuss how later.
I = date last worn. This will be calculated by excel, I’ll discuss how later.
J = number of times worn. This will be calculated by excel, I’ll discuss how later.
K to infinity = dates. Create a new column for every day. So if you start your spreadsheet with what you wear on Friday, it will begin with 11/18/11. This will be your far right column, and as time progresses you will add new columns in column K. I started out creating a week’s worth of new columns at a time so that I don’t have to create a new one every day. I’ll explain how to do input dates for a month at a time in the advanced section.
Inputting items:
With the columns created, now you can start inputting items into your spreadsheet. Fill in as much information as you can for columns A through G. If you decide that you need more or less columns to adequately describe your items, change them however you want.
Now we get to the formulas:
H = cost per wear. This is the cost (G) divided by the number of times the item was worn (J). The formula for your first item will be =G2/J2 The = is what tells excel it’s a formula. You can then click on cell H2 and drag down to the last item on your list (for me it’s H71) and click “Fill” (it should be in the top right of the menu bar) and then “Down.” This means that the formula at the top is applied to all of the cells below it (going downward).
I = date last worn. This is the most recent date that you wore the item, which is the largest number of all of the dates in your spreadsheet. So on the second day of entering information, the formula for your first item will be =LARGE(K2:L2,1) To break that formula down, column K is the most recent day in your spreadsheet, and L is the first day in your spreadsheet. The last day in the formula will change automatically as you add a new column every time that you add a new day to the spreadsheet. So after 10 days the formula will be =LARGE(K2:T2,1). However, as you add new days the formula will adapt to make the first day not K. So if on day 3 you add a new column, the formula will change to =LARGE(L2:M2,1) (advancing K by 1 and L by 1 so that the formula matches the cells it was originally assigned to, even though those cells now have new names). You simply need to go in and change the first datapoint to K2, so that on day 3 the formula is =LARGE(K2:M2,1). Use the fill down function used in H to update the whole column.
J = number of times worn. This simply tallies up how many dates you have entered for items worn. For the second day, this formula is =COUNT(K2:L2). Like for date last worn, the latter datapoint will advance automatically, but you will need to adjust the first one to stay at column K. On day 3 the formula should be =COUNT(K2:M2), and on day 10 the formula should be =COUNT(K2:T2). Use the fill down function explained in H for this column as well.
FINALLY, at the bottom of columns G, H, and J I have the average for each column so that I have the average cost, cost per wear, and number of times worn per item. I've also added up column G to get the total cost of all of my clothes. You can also average the year column to find out when most of your stuff was purchased.
This should be more than enough to get you started. Feel free to just email me for a template that you can fill in, and hopefully that will make the learning curve a bit easier.
Advanced directions are in the response section.
