A New Tracking Spreadsheet, Part I: Stats

I've talked before on my blogs about the tracker. My physical tracker journal has gone through some iterations too. I know other people will start a new journal when the old one fills up, but I don't want to continuously make the same spread over and over and keep track of 800+ TBR books every new journal. Instead I went with a Happy Planner styled journal so that it can be continuous.

But what about digitally? I don't carry my very large journal with me everywhere, which means when I get a hankering to stop at a book store... What's actually on my list? What do I already have?

Enter My Ultimate Reading Tracker on Google Sheets.

I started with G's CAWPILE sheet. I broke it when I didn't pay attention to the warning that downloading to Excel will break the charts. I don't always follow instructions.

I modified it heavily. Then she came out with v3. I modified that too. I still wasn't happy because I wanted more. And in some places, a little less.

I found Renee from AddictedToRomance.org and her Google Sheet, which in and of itself is an amalgamation of what she liked from other creators' sheets, including Ezra from I Heart Romance, Noveltea, Brock and Ali. I don't even know who those people are, but since I took Renee's sheet and modified the heck out of it, I figured I should at least acknowledge the ancestry of the thing.

The Stats

First and foremost I loved how the first sheet was an instructions page. This time I read the instructions thouroghly. Of course, with the amount of formulae I put in this thing and the number of pages I created, the Welcome page eventually was deleted because this puppy is a beast in size, but I refer back to my original copy on file when necessary.

The next couple sheets are various types of stats. 

Summary

This page is so gorgeous! It covers basic stats for the whole of the year. Also included below the charts pictured below are side by side tables where you can enter your most and least liked book for each month.

Addicted to Romance's Summary Page

Now I don't read Romance as much as this sheet is designed for and I do not consider Dystopia a genre so much as a subgenre for example, so I heavily edited these two categories on my version. I loosely follow Tonya Thompson's article "144 Genres and Subgenres for Fiction Writing" for genre classification. She obviously doesn't cover nonfiction in that article so I created my own genre/subgenre classifications for that and made Poetry its own genre between the Two Bigs. 

I also have a Fiction Genre called "Literary" which includes subgenres like Contemporary, Cultural, and Historical. I use this to classify books like The Last Anniversary by Liane Moriarty and The Tea Girl of Hummingbird Lane and Snow Flower and the Secret Fan by Lisa See. I do not see them fitting into the main genres of Fantasy, Horror, Mystery, Romance, Science Fiction, Thriller and Suspense, or Western. I've left growth for 3 more Fiction genres and 3 Nonfiction. I intend to use this as my template going forward for years to come because this baby can grow!

My Remodeled Summary Sheet

For my subgenre chart, I do not list every single subgenre in the list I use. There are 136 of them after all. Yes, the article says 144 and I added my own in, but every one of the primary ones has a Young Adult subgenre, Historical and Cultural can be used for several, and there's quite a few crossovers between Thriller & Suspense and Mystery, Fantasy and Science Fiction, ... You get the point. My previous Excel doc would give a list of subgenres based on the genre you chose, but I chose not to attempt doing that with this one. I was learning way too many other formulas to get this thing to do what I wanted it to do.

As a result, I chose to list those subgenres that I lean heavily towards and those subgenres that you probably will not catch me dead reading. I use color to draw the eye, purple being my favorites, red being my least favorites. One of my goals last year and this year has been to push myself outside of my normal and safe little bubble. I'm reading authors and subgenres and topics I'd never heard of before.

Yes, I learned how to AUTOMATE the best and worst
books charts based on CAWPILE rating!

Details

The next sheet gives you an overview of various breakdown stats per individual months, along with information about the authors, series, and formats of the year's books. It also has space to show the diversity, the longest and shortest books of the year (which I moved to the yearly Summary page), and a "books published this year" vs. "books published previously". I very rarely read NEW books. This was pointless for me so I removed it. 

Addicted to Romance's Details Page

I don't do Readathons per say. I do year long reading challenges and have started following Reese's Book Club and Emma's Our Shared Shelf, but I didn't need that info here. I removed so much from the Details sheet and then remodeled it using info I wanted to see at a glance, that it doesn't even look the same. You will also see that some of the stats are bolded and a different color. Each column has a threshold that is a rough goal per month.

My Remodeled Details Sheet

I also brought over 3 charts from the charts page, Books per Month, Pages and Hours per Month, and created my own chart, comparing the number of reads, rereads, DNFs, and Acquisitions. That big spike is because my sister introduced me to BookBub. *facepalm* 47 free eBooks in one day. Now I just have to read them!

Yes, March was a not so good month.

Monthly Stats

This page is entirely too overwhelming for me. It further breaks down the monthly books and repeats a lot of the information found on the previous two pages while expanding other peices of data. I decided to eliminate usage of this page because I migrated the stats and codes I wanted over to the first two sheets. Also, it would have required too much modification to use my genres/subgenres, book formats, etc.

Addicted to Romance's Monthly Stats page.

Charts

For the moment I am skipping over Reading Log. The original Charts page looks very similar to the cute chart page that is provided with Book Roast's sheet too. I have not fully revamped this as I'm not sure which charts I really will keep. Since Renee's Reading Log goes in DEEP to the stats on a book, the sky is the limit. I know I want to keep charts for Genre, Reading Method, Book Length, Series vs. Standalone, and maybe something related to Publication Year and the status of the author to my "library", but since these charts only gather information as entered into the Title column, it's a little problematic.

For the most part the charts do not look at whether or not the book is classified as Read, Currently Reading or DNF'd. I haven't edited charts enough to really forumlate how to get them to truly show the data I want to see.

Reading Log

The last sheet I'm going to cover in this post is the Reading Log. This is by and large my favorite of Renee's sheets. It has every last thing you might want to track on your books. I still heavily modified it, but found it was necessary to make some of my other formulas work. I also removed the Readathon columns because it took up too much space on the screen for my tastes.

I loved the inclusion of so many of her columns. You're just going to have to go check it out for yourself. Always remember that when you are using a copy of someone else's sheet and it has something in it that you don't want, you can always delete or hide that column. Same with the sheets if you're using Google Sheets. Just right-click, HIDE. It gets it out of your way and doesn't risk damaging any of the formulas. Unless you are fine with spending hours rewriting code like my dumb ass....

With my own taste tweaks included, below are some images of my Reading Log as it's been filled out the past six months.

Edited and Color coded Read Statuses

Changed 5 Readathon columns to 2 Checkbox columns

Created a formula to round half stars up or down for Goodreads

Next time on Her Royal Pinkness Reads...

I'm going to go over the various subtrackers included in Addicted to Romance's original template and what I came up with in place of or in addition to.

I've been working on this project now for about 2 months and I cannot believe the number of new formulas I have learned to get these data points manipulated in a way that quickly shows me what I actually want to know. The one I am most proud of is having the sheets automatically fill in my best rated and worst rated book of the month.

Happy Reading!

WARNING! GEEKINESS AHEAD! If formulas and geek speak are not your thing, scroll down to the comment section for any questions or comments, hitting the like button, whatever. You have effectively read the entire post. 

For my fellow geeks out there, I'm going to close with the formula for automating what I call my Monthly Raves and Monthly Rants charts.

First, I had to include a column to the left of the book title that equals the CAWPILE Category Average column. I decided to use the average of the 7 categories instead of the star rating because there isn't a difference in star rating for a 7.86 average and 7.64 average. January was a month of 3 4-star reads. How do we figure out which one I liked best?? 

In mine, the average is waaaaaaay to the right at column BF. I selected the cell that sat at E2. In the Reading Log images above, this column is hidden. I typed in the forumula =BF:BF and pressed Enter. I moved back to that cell, grabbed the little square at the lower right corner of the cell and dragged it all the way to the bottom cell of the spreadsheet. I also number formatted this column to only include 2 decimal places otherwise it goes as far as the computer will calculate the average and looks messy.

The original document went to cell 1001. That's way more books than what I would actually read in a year, so I deleted 502 on down. This gives me space for 500 books, which I will never reach, but I watched a video by a woman who read over 600 the first year of the pandemic, so you never know!

After hiding this duplicated column, and yes, for the forumula, it must be to the left of the column where your title lies. Anything more than immediately left kept causing me problems, so just put it immediately left and hide it and forget about it. 

I went back to the summary page. Here's the formula for Best of January:

=IF(Details!C5>0, VLOOKUP(MAXIFS('Reading Log'!$E$2:$E, 'Reading Log'!$G$2:$G,"Read",'Reading Log'!$A$2:$A, $J$4),'Reading Log'!$E$2:$F,2,0)

,"Not Listed")

Let's break this down. Cause that's some sheer crazy.

We're stacking functions. To start with, the center MAXIFS gives us the maximum value based on more than one set of criteria. For the Monthly Rants section we use MINIFS instead as they function identically. In this case we need to look at other pages, and to do that, we use 'Reading Log'! and Details!, (bracketing the page name in ' ' is necessary when the page name has spaces.)

The first value is stating the range this formula will be using, the hidden column we just made with the averages. 

The second value is the first criterion range, in this case, I only want to look at books that are actually finished, therefore I wrote in column G, and the third value is the actual criteria "Read". 

The fourth value is the second criterion range, the month the book was finished, found in column A of the Reading Log. The fifth value is a cell name, J4, where I have written in January. Maybe one year I want to do seasonal tracking rather than monthly... This means I don't have to rewrite every single formula, just change what is written in J4.

We have now determined that the highest value between those three averages is 7.86, for Snow Flower and the Secret Fan by Lisa See. If we just used the MAXIFS, the cell would simply read 7.86.

The next stacked function is VLOOKUP. VLOOKUP can only look for a value vertically and then 'look' to the right for the data needed. Remember how I said we had to have that extra column to the left of the title column and anything other than immediately left gave me issues? I spent 4 hours trying to learn how to do this and nothing else worked, so trust me on this.

=IF(Details!C5>0, VLOOKUP(MAXIFS('Reading Log'!$E$2:$E, 'Reading Log'!$G$2:$G,"Read",'Reading Log'!$A$2:$A, $J$4),'Reading Log'!$E$2:$F,2,0),"Not Listed")

The first value of the VLOOKUP function is the info we are searching for, called the search_key. I am telling the function to look for the cell that MAXIFS has identified.

The second value of the function is the range I want it to look at, which is column E through column F. I need both columns for this to work and this might go back to the annoying immediately next to issue. 

The third value is the index of the data. So what's that mean? If column E is the first index for the purposes of this formula, column F where the title sits is the second index, therefore we input 2.

The fourth value is either 0 or 1, thank you binary coding. This tells the function that the data we're looking at is either sorted or it isn't. Default is TRUE or 1. In my case, the titles and the ratings are not in numeric or alphabetic order. The order is determined by another column entirely and has nothing to do with this function. Therefore, we have to tell the VLOOKUP that the data is NOT sorted, 0.

=IF(Details!C5>0, VLOOKUP(MAXIFS('Reading Log'!$E$2:$E, 'Reading Log'!$G$2:$G,"Read",'Reading Log'!$A$2:$A, $J$4),'Reading Log'!$E$2:$F,2,0),"Not Listed")

Finally, we're at the IF portion, everything that has not been highlighted in the formula. At the heart of this formula there is a True or False statement that needs answered: I read books this month. If true, tell me what the best one was. If false, don't give me some funky ugly error on my pretty page.

So the first value is the True or False logical expression we need answered. We need to know if the total books read that month from the Details sheet is more than 0. January on the Details page is C5. so the True/False equation is Details!C5>0.

 

So if it is more than 0, go ahead and figure out the title of the best rated book and put that in the cell, thanks. Thus the interior formulas.

The last value is telling the function what to do if that first statement is false. The quotation marks around Not Listed tell it to put that text as a value into the cell instead.

As you can see in that last image above, July reads Next Year in Havana for both best and worst book of the month because I have only read one book so far this month. March reads Not Listed, as well as August through December. I didn't get anything finished in March and August through December haven't happened yet.

This formula needs to be tweaked for the other months obviously. C5 is the cell that details how many books I read in January. C6 is February, C7 is March and so on. I also have that sheet separated into quarters, so April is C9 in my formulas, not C8. J4 is the data I want the function to look for over on the Reading Log. J5 is February, J6 is March, etc. Other than those two things, nothing else needs tweaked to kick back the correct information automatically. Changing MAX to MIN changes it from top value to lowest value.

And there you have it. The thing I am MOST freaking proud of on this sheet thus far.

HAPPY CODING!!