Popular Post TheBlaskar Posted January 26, 2022 Popular Post Share Posted January 26, 2022 I'm a sucker for stats. Unfortunately, there isn't a singular all-encompassing gaming site or app, so I hand crafted a spreadsheet that does just about everything I need it to do, and I'm here to share it with you. I've spent the last month or so tweaking it and I add to it every day to update it, I just love it. So, I thought I'd share it with you guys so that you can copy it for yourself and have the best gaming resource since 'appear offline' was created. I present to you: "The Gamer Bible" (just the first thing i came up with so screw it, it's official) Q: What does TGB do that no other sources do? A: Well, since it's in Excel, you can completely customize the order of everything. Copy a sheet and organize by 'last played,' 'date purchased,' 'time played,' 'metacritic rating,' 'my rating,' etc. It's like playing with LEGO: the only limit is your imagination. And trying to get those damn 1x3 flats unstuck. You can also add and tweak the conditional formatting as you see fit. Currently, I have Red-Yellow-Green color gradients on "Time Played" (Green being most hours played, Red being least), Days Since Purchase (Green being most recent, Red being most distant dates), and other columns like those so that you can see which games you should play more, you know, since you bought them. This has helped me go through my backlog instead of buying yet another new game. This spreadsheet also gives you data that no other source can give you, and we'll get to that. Part 1: The Home Page The Home page is simply organized alphabetically, and contains just about every piece of data aside from the exclusive data stored on "Backlog," "Scoreboard," "Most Played," and "Wrapped." Column D contains the game titles and all link to their respective PSNP page. Just to the left of that, in Column C, are your current PSNP trophy grades (manually updated given the color in Column M, which contains a formula based on your Current Trophy %). Columns A and B are reflected more in the "Scoreboard" sheet, and are organized there for easy viewing; they show what number Plat or 100% Completion it is for you. In Columns E and F are the Platinum Percentage on PSNP and the 100% Rate on PSNP. I'm not sure how to get these up automatically update, so I've input them all manually for now. If it's an "Ultra Rare," the text is automatically made black. All values enterted into E (Plat) or F (100%) are formatted automatically, blue for plat and green for 100 as they are on PSNP, albeit vivid here. As we work our way further right, we get into the nitty gritty stats (uwu, as the fellow kids say). "Last Trophy" is updated manually, but when column C has an "S" in it, the date is automatically formatted green and white, depicting Game Completion. The other dates are light-blue, the color I use to showcase user-updated data, where applicable. Columns H and I are ratings: yours (7-color scale) and metacritic (3-color scale). Your Ratings are scaled as such: 0-10 Red Text with Black/Red Fill, 10-20 Red Text, 20-40 Orange Text, 40-60 Yellow Text, 60-80 Light Green Text, 80-99 Green Text, 100 Gold Text with Black/Purple Fill bc it's cool looking but whatever your favorite colors are you can make it happen. J through M all work together. These all relate to trophy percentages, and your progress compared to the average. J is Average % Complete on PSNP. K is your current trophy % for that game. L is % needed for an "A" on PSNP (=J2*1.5), and M is your current grade, reflected as a color instead of a letter (=K2/J2). Based on the color in Column M, you can update the letter yourself in column C (the color updates automatically there given the letter entered). N through Q all work together, as well. These, unfortunately, are also updated manually as I'm not sure how to automatically update data from outside sources. N is the number of total trophies that the game has, and this column only has data if you have not started the game. These numbers are red. O is the number of total unearned trophies for games that you've started and P is the number of earned trophies for games you've started but haven't 100% completed the trophy list. Q is the total number of trophies the game has, if you've earned them all. When Q is updated, I copy/paste "DONE" across N through P. Also note: the way I've gone about this is subtracting unobtainable trophies from the trophy lists, as if they don't exist. If I come to a game late and can't 100% complete it, then I at least want to feel like I've 100% completed it for earning all possible trophies. You can adhere to them, though, and I would respect the "cutthroatedness." Column R is my favorite: hours played. If you use apps like Area X, they offer a very exact datum for each game. However, this is sometimes incorrect. For example, I've put an ungodly amount of time into some games on PS4, but when I play the PS5 version, Area X puts my time played on PS5 for both entries. So, this is an issue. But, for the data that's correct, Area X is the best source. If you don't have a PS5, then I'm not sure how to get this data. I didn't even know Sony kept my playtime until I found it on PS5. So, for PS4 users (the vast, vast majority still), I am unsure of how you can fill this column unless you play with a stopwatch on your phone as I did in 2018. Column S is tricky. I manually enterted in all of these numbers, but didn't just go to HowLongToBeat and find the completion time. No, I used guides on PSNP. Not just base game guides, mind you, but DLC guides, too. The numbers you see in this column are the estimated time it would take you to go from no trophies to all available trophies. For games that don't have full guides available, I had to supplement with additional estimations given other DLCs (if I game has 3 out of 4 DLC guides), PlaystationTrophies guides, or (as a last resort) HLTB estimates. Those are hardly accurate for trophy lists, though, just for the in-game to-do's. Column T was a bitch and a half, but I love having this data. Using Sony's website, I went through my transaction history and put in the purchase date for all games I have in my library, or on my PSNP (if I didn't 'purchase' them). Column U contains the formula =TODAY()-T2 and depicts the number of days that have gone by from purchase to today. This is on an automatically updating color scale so when you 100% complete the game that you bought longest ago, you can put "DONE" in Column U and two things will happen: the color scale will shift with the next game turning more red, and data will appear automatically in Column V. Column V will show you how many days passed from the date you purchased the game until the date you completed it (using "Last Trophy" data). At the bottom of the page (not depicted) is a row of Averages, followed by the amount of each letter grade you have, and lastly, by a list of games on your wishlist including all stats the above games have. This makes it easier to know what to buy next, and how that game will affect your stats. Part 2: The Backlog Page The most recent addition to TGB is the organization of the "Backlog" sheet: by 'Days Since Purchase.' This 'Backlog' also serves as a library of games I own but have not played yet, and those are organized along with the actual backlog. Columns C-E reflect purchase date while Columns F-H reflect the last day a trophy was earned on a game. If Column F has an X, then Columns G and H will have an error in the formula, and thus, are the same color text as the background so it looks empty. If Column F has a date, then columns G and H populate with visible data as seen in the other rows. As mentioned, C-E are organized by Purchase Date, with the most distant games being at the top. For me, the games that came with my console I still have yet to 100%. Column D is on a Green-Yellow-Red Gradiant and Column E is conditionally colored by integer (0-1, 1-2, 2-3, etc) Since this sheet is organized by D, Column G can become a little messy with the colors if you bounce back and forth between games a lot. G is on the same scale as D, with the TOP 10 numbers formatting in a white text. H is formatted similarly to E, but in halves instead of whole numbers. Because, really, if it's been 2 years since you earned a trophy on a game, you're gonna need to re-learn the mechanics. Thus, orange and red much quicker. But, if you have a good memory, then adjust the formatting so 2 years past is still yellow. Column I is also exclusive to this sheet: Easiest Unearned. I'm still working on this and it's going to have to all be manual, but basically I look at every unearned trophy for that game (Across DLC and Base Game) and copy the PSNP % for the most common trophy. And then update it every time I get a trophy from that game (every evening, really). There's gotta be an easier way to do this but I know excel- not much else. Columns J-S are ripped straight from the Home Page, except "Got Em All" is removed as any game you 100% will be removed from this sheet. I'm also considering removing J-S to keep this page from looking too busy. The Home sheet already has a lot, this one doesn't need to do everything the Home sheet does. The numbers in column A are also manually enterted (starting at the bottom with 1 and then just dragging the auto-count all the way up). This sheet has saved me a decent chunk of money this week, to be honest. I have a lot of games I haven't played and need to get on that. I can only see that easily thanks to this sheet. At the bottom are the PSNow and PS+ games, organized separately as they weren't 'purchased.' Of course, priorotize them as you see fit (ie new PS+ games or games leaving PSNow soon). The world is your oyster, and this spreadsheet is... also your oyster Part 3: The Scoreboard This sheet is my favorite, as it gives data that no other sources gives. I can't find any website that shows you what's in Column H, and I'm also very exctied to share with you what's in Cell L2. So, the SCOREBOARD sheet keeps track of your platinums and 100% completions, organized by when you got em done. It gets slightly confusing to fresh eyes, though, as you'll notice certain things: Rocket League is has an S Rating and is green, showing that it's 100% completed, but it doesn't have a number for completions (shown in gold, for fun), while God of War has both. And then Burnout Paradise has a blue Platinum number but has an A. I'm sure some of you know what's up, but the answer is in Column F. This column will tell you if the Platinum is also the 100% (no DLC trophies) or not, and if you've earned it... For "DONE" (as seen with Rocket League), I've done the DLC trophies, but I didn't when I got the platinum. It's Completion #83. For "N/A" there are no DLC trophies; a Platinum means a 100%. For "NO," there are DLC trophies but you still have yet to earn them all. It's best to think about in order of dates. Whenever you get a Plat or 100% a game, it gets added to the Scoreboard. This part's great. Column H shows you average Plat/Completion %. As you can see with Rocket League, the Plat percentage is 19.37% so my total average is 19.37%. My next plat/comp is God of War which has a 29.05% which means Column H now averages the two most recent pieces of data. Now, some may wonder why GoW doesn't count as BOTH a plat and comp % and that's because it creates an unevenly distributed weight. It's just one trophy list, one base game, only one entry. BUT, for a game such as Dragon Age Inquisition which I'm working on now, the Plat % and Comp % will both have data in there since I'm doing it at the same time. Does that make sense? The date, in that case, will be the Comp %. You can make these separate lines, instead. But, I'm pretty new to trophy hunting. So, I looked back and saw that I earned some EASY plats and was a bit ashamed. I don't want to be one of the people who has 500 Plats but they're all 98% games across multiple platforms and regions. No, no, no. I mean, you do you of course by I want my stats to be lower. So, H is on a gradient from 5% to 30%, .05 being very green and .3 being very red. Also because of my shame, I created column I: Prestige mode. Yeah, you basically get a fresh start. After your 100th completion, Column I pretends you're a fresh gamer and can get all new stats to erase your mistakes. Of course, Column H is a cruel POS so he's still got your numbers but I is a new, fresh friend. Make em happy. Maybe you're like me, though, and are a total stat slut. I give you J1 through L6. This little box does quite a bit of math for you, but you only really need to focus on the top row(s) which showcase your Current Average, Desired Average, and x% Games Needed to reach Desired Average. I made sure to be clicking on L2 when screenshotting this so that you can have the formula as it took 3 days to figure out but here it is "=(L6-(K2*L4))/(K2-0.1)" and I'm sure you can figure out the SUM and COUNTA formulas in the other boxes. I don't want to bore you with the math in this but the important part is the "0.1" near the end. This reflects what %-Completed games I need to hit my Desired Average. I thought 10% was fair but if you wanna go full Try Hard and aim for Ultra Rares, then change the "0.1" to "0.499" and let er rip. Alternatively, maybe your average is like 90% and you wanna see it around 50% so how many "0.33"s will you need? Plug in the digits. This formula took DAYS. I bestow it upon ye with tender love and grace. Muah. Part 4: Time Played (yes, I know, and I'm sorry) Look, I played it for the first time in December 2017. That was before all of the toddlers came in. And it was so much fun at first. So, yes, I may deserve those raised eyebrows but I have the right to an attorney. Anyways... So, as mentioned, PS4 doesn't show you how long you've played each game. But, it does save it. For PS5 users, you can see how long you've played every game- across PS4 and PS5 and maybe PS3 too idk for sure I was a 360 guy. For this single sheet, I also put in my Switch stats, and Xbox One stats when I get it back from my buddy, and anything else that keeps track of gametime. I want this to be all-encompassing. I will admit, the colors are a bit of a mess. The colors reflect a different system from the Month-Week-Day-Hour ranking system. Previously, 500+ games were purple, 200+ games blue, 100+ green, but then I added the weeks so it doesn't look right atm. but, you can create whatever system you want based on how much/little you play your average game. It's all up to you. It's excel. In Column D, you'll see the Platform, colored based on trophy status (not complete, platinum, 100%), and the stats for those categories just to the right in E-F (same stats as the home page). Same with G which depicts your trophy percentage as found in many of the other sheets. Currently, these numbers are manually entered but you can make G2 on this sheet equal K173 on the home page so that you only have to update it in one location and it'll do the rest for you. That's what I'm working on next, having the HOME sheet be the hub and all subsequent sheets get their data from HOME instead of it being manual each time anything changes. Parts 5 and 6: Wrapped and 2022 Stats I really wanted to make a Spotify Wrapped but for my gaming. Sony did one this year (idk about last year) and it was great, but I wish it said more, like Spotify does. So, I created a mini version of it for fun. You don't have to update this too often, I mean you could do it on 12/31 every year and call it a day. I love having this data, though, and these pages are huge for me. On the Wrapped page, the white text indicates records (the most trophies, the most ultra rares, the rarest trophy, the most-played game I have on record, etc) Also Note: The main thing you'll notice is that there are no images here or anywhere on this document. I've tried on Google Sheets and I've tried on Excel. It's never perfect for a large amount of entries: Sheets has trouble loaded and Excel will start stacking pics and move their locations. If you figure out how to put a pic IN the cell instead of on it in Excel, let me know. It seems like you have to save all images individually and place them individually, which I'm down to do but I don't want to spend 3 days doing that and then have it not work properly. Lastly, here's the link for the GOOGLE SHEETS version of the excel file. I tried to put the excel file in here but it didn't take. The sheets version doesn't have much of the formatting or style, but it has the basic necessities. Might be a good place to start if you wanted to copy it and make your own version; the formulas are there for you https://docs.google.com/spreadsheets/d/1sCPJv994hKRaToVK-mZ2v9LBa26BZLdJ/edit?usp=sharing&ouid=101159915741781318405&rtpof=true&sd=true Anyways, that's really all. I've just been super proud of this thing and wanted to share it with you guys as none of my friends could possibly care less about it. Happy hunting! 5 Link to comment Share on other sites More sharing options...
Gabriel2Silva Posted January 1, 2023 Share Posted January 1, 2023 I'm a complete dummy when it comes to spreadsheets. This one is simply amazing, the best one I've ever encountered online. Would you kindly send the Excel version of this document via PM? Thanks in advance! Link to comment Share on other sites More sharing options...
Annoyingtiger888 Posted January 3, 2023 Share Posted January 3, 2023 Holy crap, the dedication and time this must have taken. It looks amazing, mad props, man. Link to comment Share on other sites More sharing options...
SunkenQueen Posted January 8, 2023 Share Posted January 8, 2023 This is a godsend. Thank you Link to comment Share on other sites More sharing options...
Viper Posted January 8, 2023 Share Posted January 8, 2023 Damn this was posted nearly a year ago and no one has said anything until now? This is damned impressive. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now