13 July 2013

Love Varnish goes educational - How to make your own spreadsheet part 2!


Hi loves! Are you ready for another educational post? I hope you are :-). If you haven't read part 1 yet, I advice you to take a peak and learn some basics, because today I'll be talking more about statistics and what your spreadsheet can actually do for you.

Besides my need for organization, I also love statistics. How many polishes do I have, how many of them are tried, how is the balance between indie and regular brands? All this kind of info is already in my spreadsheet, but you can also make it show up in any way you want!

We are using my own spreadsheet again as the reference, you can find it here. I already mentioned I have 2 sheets withing my workbook, one called Stash list and the other Statistics. The Statistics sheet isn't filled manually but automagically based on the formulas I've entered.



Why don't we start at the basics? Formulas are preset calculations, they calculate everything you tell them to. If there is any irregular info in your formula, it will not work. Every time you alter the data the formula needs to do his job, the endresult will be calculated again and again and again. A basis formula that is very simple and effective, is =SUM(data:data). = makes sure the formula is recognized, SUM says I want to add up data, (data:data) determines the range which is used for the formula.

The formula always shows up underneath the toolbar


Now, let's take a look at both kind of formulas I have in my Statistics sheet:
COUNTIF - This is the formula that easily tells me how many polishes there are that meet a given condition. The condition can be a brand name, if a polish is untried, the amount of coats a polish needs, it's all possible! As an example let's brake down the formula for cel C2
The formula is: =COUNTIF('Stash list'!A2:A5035; "X")
= (start of formula), COUNTIF (counts how many times a cell meets the given condition), 'Stash list'!A2:A5035 (data range that is used), "X" (the condition I want the formula to count). I mark every row that is filled in my spreadsheet with an X.
Percentage calculation - One of the easiest formula's, but I like to see what portion of my stash is untried for example. Example formula comes from cel D3.
The formula is: =C3/C2.
= (start of formula), C3 (number for which you are calculating a percentage), /C2 (divided by the amount that is your total stash).

That wasn't too hard now, was it? I hope you found this second part helpful and please let me know if you expanded your spreadsheet or even started one because of these posts!

6 comments :

  1. Oh, this is going to be so helpful. Thanks ever so much! I really needed this. Now the overwhelming task of getting started....so many polishes, I'd rather be painting instead of organizing. But gotta start somewhere. Thanks again!

    ReplyDelete
  2. This is great! Right now I only have a list on my blog (which I keep up-to-date), but this has inspired me to make a spreadsheet. I'm also a geek when it comes to spreadsheets and stats so I'm going to have fun creating one. :-)

    ReplyDelete
  3. Once you get a good start it is much fun :D!

    ReplyDelete
  4. great ideas! I do have a spreadsheet on Google Docs but until now I have not added statistics to it - maybe I am too used to Excel pivot tables, but with a couple of count formulas and some more data consistency, I should be able to play around nicely. And maybe to have a clearer vision of what I need and what I don't (wishful thinking, hehe...)
    Thanks again :-*

    ReplyDelete
  5. You are welcome Julie! Glad it is of help :).

    ReplyDelete

Hi loves! If you are seeing this message, it means Disqus has not loaded yet. Please wait a second, as soon as this message disappears Disqus will load and I will see your comment properly :). Thanks ♥.