User Score Statistics Excel Spreadsheet

[voidedalive2x]

Putting this here because I'm not sure it'd fit anywhere else.

Anyways, UltraHeroABC5 was interested in figuring out how I made my Score Statistics Excel Spreadsheet that keeps track of my standings on the User Score Statistics page, which I usually update that weekend after the site does its automatic Saturday update. I'm certainly happy to show him, but I figure I might as well let anyone else who wants to track their own progress have the ability to see it instead of just PMing it to ABC. This will more-or-less be the step-by-step process of how I recall that I made it.

Disclaimer: I do not claim to be a whiz at using Excel, nor do I claim to have made the best possible way to track these. If there is a better method out there (or some awesomazing macro that DrSham could whip up), by all means use it because I'll probably try to switch to that instead.

I also will try to point out what shortcuts I'm using if I use any at all, and be as thorough as possible in showing this, just in case those who peruse this aren't that Excel-savvy. Some of you may be better at using Excel than I am and can probably just skip to the actual directions, but I do want to make sure that anyone looking can follow me, so some apologies in advance if I sound too teacher-y.

----
----

So, with that out of the way, let's get to it.

Step 1 is getting our rows set up for where our numbers will go in. Once you have your blank spreadsheet, put the following in column A, starting in A2:

Mine (this is your total as listed on the statistics page. If you're logged onto the wiki and view the page, if you aren't on the default top 50--which should be what shows up unless you have it lessened to, say, the top 10, or upped to the top 100) your total should be at the very bottom as an extra row, along with your rank.)
Next (This is the total of the next person above you in rank. If you aren't in the top 1000, which is the max number it goes to if that many users have entered at least a score for that location, just use the 1000th person's total)
Difference ( This will be what you will refer to the most often, to see how close you are to a rank up.)
Next Score Differentials (We'll get to this later on, but you'll want to include this.)

Those four are the really important ones. These next ones, however, are completely optional, but you're welcome to use them, and they go between "Difference" and "Next Score Differentials". They are:

Goal 1
Difference
Goal 2
Difference
Top Score
Difference

Goal 1 and Goal 2 are personal milestones you can set for yourself as you climb, whatever they may be. Maybe they're particular people you want to beat (in my case, that'd probably be Dramacomic and Arvain), or if you just want to track certain score increments, which is what I use them for.

Top Score is simply taking the #1 spot on each board and comparing it to your current one. In my opinion, I wouldn't necessarily consider adding this one unless you really think you can make a stab at any of the top spots. I have the 5th highest number of unique scores on site, and I only have about 4 top scores personally (PS2, Keys, GHA and Green Day Rock Band), So I suggest you only add this if you really think you can do it. Or if you just want to see how close you're getting to the top.

If you used all of these, your sheet should look like this.

The next part is that we're going to merge and center each of those with columns A and B. Just these, mind; you'll want to use the lower portion later on. Just select A2 and B2 together, and click the Merge and Center button. Then select the black square at the bottom right of the cursor and drag it down to cover the rest. A box called "Autofill Options" should show up, and then you'll want to select "Copy Formatting Only" so that they'll keep their original content instead of all of them just saying "Mine" like the birds from Finding Nemo. We don't want that.

At the end, it should look like this. I unfortunately can't show you the black square when I go to take screencaps with Gyazo, but it shouldn't be hard to find.

The very last thing we'll do here is put a freeze on Columns A and B and Row 1. Go under the View tab, click freeze panes, and choose the first option. Make sure your Excel cursor is on C2 so that it'll work properly. It should look like this if you've done it right. An easy way to check is to scroll all the way to the right of the spreadsheet, and you should always see those words to your left no matter how far right you go.


----

Step 2 will involve inputting the actual totals that you want. Nothing else we plan to do can really be done without them (well, technically we could do the formatting before actually retrieving the scores, but I think you should leave that till last so you're not trying to check each individual cell, row or column to find that one errant calculation).

So, first of all, you need to decide what boards you want to track. If you're an expert-only player you don't need to include the Easy, Medium, or Hard difficulty boards, and vice-versa if you can't or won't touch Expert. How exactly you list them along the top row is up to you, but be sure that you include BOTH the boards you have scores on and any boards that you plan on being on or being serious about climbing in because if you decide you don't want to include, say, GHWoR in your boards, and then decide after the fact that you want to try for it after all once it's all set up, it's gonna be harder to insert it then than it is now.

Your best bet for listing them is to just place them as they appear on the statistic boards, but if you want your easy board next to guitar, which is next to GHWT, be my guest.

(An important thing to note here: As mentioned near the start, we are taking scores from the Unique score Total here, which totals every unique score, aka your only/ranked score (or best if you have more than one score inputted for one song; that's covered in the overall totals). This counts for all scores across all available categories that apply. If you're on, say, GH3, and you have scores in for every song on PS2 and PS3 on Easy through Expert, plus DLC on PS3 or the 360 on Expert only, will count each and every one of your best scores for all of those, DLC included. If you feel like your total is farther down than it should, especially if you've played every on-disc song on every difficulty, its probably because those higher than you have either played all the DLC if applicable or played every on-disc song on more than one console. tl;dr: Don't try for top score on GH2 unless you have a PS2, a 360, and all ~23 DLC songs. You won't make the top 50 without them. I know because I've got all possible scores for GH2 on PS2 with a less than 3 million margin from the top score, but I'm still only just above the top 200. It sucks ass.)

This is how I have mine listed, more or less. This is also where your headings should be, and if your frozen cells are placed correctly these should stay at the top no matter how far down you scroll.


Next, you need to input both your current point totals for the boards you've selected in the "Mine" row, along with the next available score in the "Next" row. Once you have those, you'll subtract "Next" from "Mine" and that total should go in the first "Difference" row.

If you've got the totals and calculations right for one column, your's should look somewhat like this, though obviously the actual numbers will vary from person to person (I used my "All" totals for demonstration). The negative number is in parenthesis because if you use the Comma Style option like I do it defaults to using the Accounting Style without the currency sign ($, etc.). I really wish it didn't, but it serves. Don't forget to remove the decimals, too!

If everything looks right, now you can start putting in the rest of your scores and for whatever other rows you added. Don't forget you can autofill the difference calculation across the row by selecting the black box on the cell cursor and dragging it across; it'll save you a LOT of time against manually inputting it for every column! If you need help, here's what your formulas should be like:

the Next Differential should be "Mine-Next"
The Goal 1 Differential should be "Mine-Goal 1"
The Goal 2 Differential should be Mine-Goal 2"
The Top Score Differential should be "Mine-Top Score"

to use my "All Scores' Column as an example, Next Differential is C2-C3; Goal 1 Differential is C2-C5, Goal 2 Differential is C2-C7, and Top Score Differential is C2-C9. Mine looks like this. The dashes on the other cells in rows 5, 7 and 9 are because I don't have numbers in the cells they're pulling from, and while the number is basically 0 there's no "0" to pull so that's the placeholder.

This probably what yours will look like once you've got all the scores in. I've already done some extra formatting to make the larger numbers fully visible, which I'll cover below, and while I don't have Goal 1 and Goal 2 numbers down I'll take care of that on my own later.

----

Now that you've done the 2nd most time-consuming part of this entire project, now it's time for the most time-consuming: Formatting! Yay!

...

...*ahem* moving on.

There's at least three different things you're going to do if you want your finished product to look like the image I showed you at the very beginning: Coloring the cells, marking the difference formulas as exact, and copying the Next Differentials to the frozen area.

First up is coloring the cells. For this I simply used a very basic red-yellow-green scheme. Red is for the totals where my score is lower than the one I'm shooting for; yellow is for when they're equal; and green is for when I'm over.

First you'll select each of the differentials in your first column--you can either ctrl+click to select them without choosing any other cell or do this individually. Then you'll select the "Conditional formatting" option under the styles section and use these conditions:

IF the total is LESS THAN 0 (aka negative), use a RED fill with DARK RED text
IF the total is EQUAL TO 0, use a YELLOW fill with DARK YELLOW text
IF the total is MORE THAN 0, use a GREEN fill with DARK GREEN text

These three options should be the first ones you see when you check the drop-down options in the window that'll pop up for each one. This is what the cells will look like when those conditions are met, and this is what the actual column should appear as. You can't see the other formatting as the numbers in there are less than 0 (and for the most part should stay like that if you're trying to copy me exactly), but if I ever got a total over the next guy the Next Differential would turn green or yellow if mine was equal to or greater than theirs. Once you're sure it's working, simply autofill it in down the line.


The next formatting you'll need to do is to make each calculations go to EXACTLY those specific cells. This is very important because the next bit we'll be doing will involve copypasting the scores in the Next Differential to different cells, and unless you make them on exact cells it'll look completely screwed up when you do it.

What you'll need to do is select the cell of the Differential, entire formula in the formula bar and press F4 (fn+f4 for those of you who have it that way; I don't know the command for Mac, though). the "C2" should then look like "$C$2", and that lets you know that, no matter where you copy that particular cell, it will ALWAYS pull data from C2 no matter what. Now do the same for the other cell in the equation.

Now, here's the real time-consuming part: You'll have to go to each individual cell, and do that for each calculation. Yes, I know, it sounds so dumb, so boring, and will take so long, but that's the way it's programmed. You can't use the autofill because if you did every cell you filled would pull from the same cell or cells instead of the ones they need to, and because you have to do this to every individual cell number you can't just select every cell you want to F4 it all. The good news is that you only need to do it to certain cells, not every single one, so it shouldn't be that time-consuming, but I sympathize with you anyways. This is what mine looks like for the Next Differential under the "All" column.


So, why'd I tell you do all of that work? Here's why. First, go across the very first row (the one that's frozen) and copy all the games you've put columns up for. Now go to the first cell under Next Score Differentials (in the B Column), and right-click. Under Paste Options select the "Transpose" option. It should take all of those items and, instead of copying them horizontally along the spreadsheet, they should instead go vertically down column A. It should look like this.

Now, you're going to take the scores under the Next Differential column and do the exact same thing in Column A. THIS is why you needed to do the exact cell formatting. If you don't, it'll do this. However, if you did do the exact cell formatting, it should instead look like this. Every total in the columns should match the same one from the original row.

(sidenote: the pictures I have don't match the instructions, mainly because I forgot which side I had the numbers on. You do need the numbers on Column A for a bit of future formatting, though.)

There's two final bit of formatting you'll need to do. First you need to select all those cells again (the one from the Next Differential) and go to the Conditional Formatting option again. This time, instead of choosing greater than/lesser than/equal to, you'll go the Color Scales option and choose the first option. It'll be the right one if any positive values are greener and negative values are redder. If it's right, it'll look somewhat like this.

The final step in all of this is to sort it all properly. You'll select both columns, right-click, go to the sort option, and click custom sort. In the window that pops up, you'll want to choose to sort by Next Score Differential (which is why I have the numbers in Column A), leave the Sort On as Values (or change it to that if it isn't there), and put the order as Smallest to Largest. This will put the most negative totals at the bottom, and the least negative at the top. If you did it right, it should look like this.

Then, if you want to do the same for the top scores, just repeat the same process for the Top scores.

----

And that's everything. Now we can copy-past the entire sheet into a new one and do the same for the overall totals...wait, why are you walking away? No, don't leave yet, I still have to tell you some stuff! Come baaack!

----
----

Anyways, there you have it. That should give you a spreadsheet that looks pretty darn close to mine. And really, you can pretty much copy-paste the spreadsheet to a new one and use that for Overall totals, though you'll have to go and get those specific totals.

As far as updating goes, you'll have to wait until the page auto-updates, which is every Saturday at Noon Eastern Time (I think; I know it updates at 1 for me, and I'm Central Time, so it's either Mountain or Eastern), then go through each board and update all the necessary totals. Most of the time you'll have to update yours, next, and top scores for each column, unless you meet a goal, and sometimes you only have to change yours if you didn't get a rank-up and the top scorer didn't increase their total.

When you're all updated, re-sort the differentials on the side, and then just choose the least negative one to work on. Rinse and repeat this until you reach your desired goals and/or get bored of doing that or GH/RB.

----

If you made it through this entire thread, congratz and thank you for reading this! Please don't hesitate to point out any errors in spelling, grammer, or if any of the images are wrong or don't match up with what you're getting. And don't hesitate to ask if anything I said didn't make sense. I do wish I had started doing this much earlier as it would've made making goals a lot easier to do and to follow, but I guess it's too late for that. Oh well.
_________________

GH accomps: FFWF, DE, Battery HDrums FCs! HDrums 46/49!!
RB accomps: RB3 MDrums FGFC! RB3 MOMBFGFC!
Old Youtube, New Youtube | I do Twitch, now!

[voidedalive2x]

Putting this here because I'm not sure it'd fit anywhere else.

Anyways, UltraHeroABC5 was interested in figuring out how I made my Score Statistics Excel Spreadsheet that keeps track of my standings on the User Score Statistics page, which I usually update that weekend after the site does its automatic Saturday update. I'm certainly happy to show him, but I figure I might as well let anyone else who wants to track their own progress have the ability to see it instead of just PMing it to ABC. This will more-or-less be the step-by-step process of how I recall that I made it.

Disclaimer: I do not claim to be a whiz at using Excel, nor do I claim to have made the best possible way to track these. If there is a better method out there (or some awesomazing macro that DrSham could whip up), by all means use it because I'll probably try to switch to that instead.

I also will try to point out what shortcuts I'm using if I use any at all, and be as thorough as possible in showing this, just in case those who peruse this aren't that Excel-savvy. Some of you may be better at using Excel than I am and can probably just skip to the actual directions, but I do want to make sure that anyone looking can follow me, so some apologies in advance if I sound too teacher-y.

----
----

So, with that out of the way, let's get to it.

Step 1 is getting our rows set up for where our numbers will go in. Once you have your blank spreadsheet, put the following in column A, starting in A2:

Mine (this is your total as listed on the statistics page. If you're logged onto the wiki and view the page, if you aren't on the default top 50--which should be what shows up unless you have it lessened to, say, the top 10, or upped to the top 100) your total should be at the very bottom as an extra row, along with your rank.)
Next (This is the total of the next person above you in rank. If you aren't in the top 1000, which is the max number it goes to if that many users have entered at least a score for that location, just use the 1000th person's total)
Difference ( This will be what you will refer to the most often, to see how close you are to a rank up.)
Next Score Differentials (We'll get to this later on, but you'll want to include this.)

Those four are the really important ones. These next ones, however, are completely optional, but you're welcome to use them, and they go between "Difference" and "Next Score Differentials". They are:

Goal 1
Difference
Goal 2
Difference
Top Score
Difference

Goal 1 and Goal 2 are personal milestones you can set for yourself as you climb, whatever they may be. Maybe they're particular people you want to beat (in my case, that'd probably be Dramacomic and Arvain), or if you just want to track certain score increments, which is what I use them for.

Top Score is simply taking the #1 spot on each board and comparing it to your current one. In my opinion, I wouldn't necessarily consider adding this one unless you really think you can make a stab at any of the top spots. I have the 5th highest number of unique scores on site, and I only have about 4 top scores personally (PS2, Keys, GHA and Green Day Rock Band), So I suggest you only add this if you really think you can do it. Or if you just want to see how close you're getting to the top.

If you used all of these, your sheet should look like this.

The next part is that we're going to merge and center each of those with columns A and B. Just these, mind; you'll want to use the lower portion later on. Just select A2 and B2 together, and click the Merge and Center button. Then select the black square at the bottom right of the cursor and drag it down to cover the rest. A box called "Autofill Options" should show up, and then you'll want to select "Copy Formatting Only" so that they'll keep their original content instead of all of them just saying "Mine" like the birds from Finding Nemo. We don't want that.

At the end, it should look like this. I unfortunately can't show you the black square when I go to take screencaps with Gyazo, but it shouldn't be hard to find.

The very last thing we'll do here is put a freeze on Columns A and B and Row 1. Go under the View tab, click freeze panes, and choose the first option. Make sure your Excel cursor is on C2 so that it'll work properly. It should look like this if you've done it right. An easy way to check is to scroll all the way to the right of the spreadsheet, and you should always see those words to your left no matter how far right you go.


----

Step 2 will involve inputting the actual totals that you want. Nothing else we plan to do can really be done without them (well, technically we could do the formatting before actually retrieving the scores, but I think you should leave that till last so you're not trying to check each individual cell, row or column to find that one errant calculation).

So, first of all, you need to decide what boards you want to track. If you're an expert-only player you don't need to include the Easy, Medium, or Hard difficulty boards, and vice-versa if you can't or won't touch Expert. How exactly you list them along the top row is up to you, but be sure that you include BOTH the boards you have scores on and any boards that you plan on being on or being serious about climbing in because if you decide you don't want to include, say, GHWoR in your boards, and then decide after the fact that you want to try for it after all once it's all set up, it's gonna be harder to insert it then than it is now.

Your best bet for listing them is to just place them as they appear on the statistic boards, but if you want your easy board next to guitar, which is next to GHWT, be my guest.

(An important thing to note here: As mentioned near the start, we are taking scores from the Unique score Total here, which totals every unique score, aka your only/ranked score (or best if you have more than one score inputted for one song; that's covered in the overall totals). This counts for all scores across all available categories that apply. If you're on, say, GH3, and you have scores in for every song on PS2 and PS3 on Easy through Expert, plus DLC on PS3 or the 360 on Expert only, will count each and every one of your best scores for all of those, DLC included. If you feel like your total is farther down than it should, especially if you've played every on-disc song on every difficulty, its probably because those higher than you have either played all the DLC if applicable or played every on-disc song on more than one console. tl;dr: Don't try for top score on GH2 unless you have a PS2, a 360, and all ~23 DLC songs. You won't make the top 50 without them. I know because I've got all possible scores for GH2 on PS2 with a less than 3 million margin from the top score, but I'm still only just above the top 200. It sucks ass.)

This is how I have mine listed, more or less. This is also where your headings should be, and if your frozen cells are placed correctly these should stay at the top no matter how far down you scroll.


Next, you need to input both your current point totals for the boards you've selected in the "Mine" row, along with the next available score in the "Next" row. Once you have those, you'll subtract "Next" from "Mine" and that total should go in the first "Difference" row.

If you've got the totals and calculations right for one column, your's should look somewhat like this, though obviously the actual numbers will vary from person to person (I used my "All" totals for demonstration). The negative number is in parenthesis because if you use the Comma Style option like I do it defaults to using the Accounting Style without the currency sign ($, etc.). I really wish it didn't, but it serves. Don't forget to remove the decimals, too!

If everything looks right, now you can start putting in the rest of your scores and for whatever other rows you added. Don't forget you can autofill the difference calculation across the row by selecting the black box on the cell cursor and dragging it across; it'll save you a LOT of time against manually inputting it for every column! If you need help, here's what your formulas should be like:

the Next Differential should be "Mine-Next"
The Goal 1 Differential should be "Mine-Goal 1"
The Goal 2 Differential should be Mine-Goal 2"
The Top Score Differential should be "Mine-Top Score"

to use my "All Scores' Column as an example, Next Differential is C2-C3; Goal 1 Differential is C2-C5, Goal 2 Differential is C2-C7, and Top Score Differential is C2-C9. Mine looks like this. The dashes on the other cells in rows 5, 7 and 9 are because I don't have numbers in the cells they're pulling from, and while the number is basically 0 there's no "0" to pull so that's the placeholder.

This probably what yours will look like once you've got all the scores in. I've already done some extra formatting to make the larger numbers fully visible, which I'll cover below, and while I don't have Goal 1 and Goal 2 numbers down I'll take care of that on my own later.

----

Now that you've done the 2nd most time-consuming part of this entire project, now it's time for the most time-consuming: Formatting! Yay!

...

...*ahem* moving on.

There's at least three different things you're going to do if you want your finished product to look like the image I showed you at the very beginning: Coloring the cells, marking the difference formulas as exact, and copying the Next Differentials to the frozen area.

First up is coloring the cells. For this I simply used a very basic red-yellow-green scheme. Red is for the totals where my score is lower than the one I'm shooting for; yellow is for when they're equal; and green is for when I'm over.

First you'll select each of the differentials in your first column--you can either ctrl+click to select them without choosing any other cell or do this individually. Then you'll select the "Conditional formatting" option under the styles section and use these conditions:

IF the total is LESS THAN 0 (aka negative), use a RED fill with DARK RED text
IF the total is EQUAL TO 0, use a YELLOW fill with DARK YELLOW text
IF the total is MORE THAN 0, use a GREEN fill with DARK GREEN text

These three options should be the first ones you see when you check the drop-down options in the window that'll pop up for each one. This is what the cells will look like when those conditions are met, and this is what the actual column should appear as. You can't see the other formatting as the numbers in there are less than 0 (and for the most part should stay like that if you're trying to copy me exactly), but if I ever got a total over the next guy the Next Differential would turn green or yellow if mine was equal to or greater than theirs. Once you're sure it's working, simply autofill it in down the line.


The next formatting you'll need to do is to make each calculations go to EXACTLY those specific cells. This is very important because the next bit we'll be doing will involve copypasting the scores in the Next Differential to different cells, and unless you make them on exact cells it'll look completely screwed up when you do it.

What you'll need to do is select the cell of the Differential, entire formula in the formula bar and press F4 (fn+f4 for those of you who have it that way; I don't know the command for Mac, though). the "C2" should then look like "$C$2", and that lets you know that, no matter where you copy that particular cell, it will ALWAYS pull data from C2 no matter what. Now do the same for the other cell in the equation.

Now, here's the real time-consuming part: You'll have to go to each individual cell, and do that for each calculation. Yes, I know, it sounds so dumb, so boring, and will take so long, but that's the way it's programmed. You can't use the autofill because if you did every cell you filled would pull from the same cell or cells instead of the ones they need to, and because you have to do this to every individual cell number you can't just select every cell you want to F4 it all. The good news is that you only need to do it to certain cells, not every single one, so it shouldn't be that time-consuming, but I sympathize with you anyways. This is what mine looks like for the Next Differential under the "All" column.


So, why'd I tell you do all of that work? Here's why. First, go across the very first row (the one that's frozen) and copy all the games you've put columns up for. Now go to the first cell under Next Score Differentials (in the B Column), and right-click. Under Paste Options select the "Transpose" option. It should take all of those items and, instead of copying them horizontally along the spreadsheet, they should instead go vertically down column A. It should look like this.

Now, you're going to take the scores under the Next Differential column and do the exact same thing in Column A. THIS is why you needed to do the exact cell formatting. If you don't, it'll do this. However, if you did do the exact cell formatting, it should instead look like this. Every total in the columns should match the same one from the original row.

(sidenote: the pictures I have don't match the instructions, mainly because I forgot which side I had the numbers on. You do need the numbers on Column A for a bit of future formatting, though.)

There's two final bit of formatting you'll need to do. First you need to select all those cells again (the one from the Next Differential) and go to the Conditional Formatting option again. This time, instead of choosing greater than/lesser than/equal to, you'll go the Color Scales option and choose the first option. It'll be the right one if any positive values are greener and negative values are redder. If it's right, it'll look somewhat like this.

The final step in all of this is to sort it all properly. You'll select both columns, right-click, go to the sort option, and click custom sort. In the window that pops up, you'll want to choose to sort by Next Score Differential (which is why I have the numbers in Column A), leave the Sort On as Values (or change it to that if it isn't there), and put the order as Smallest to Largest. This will put the most negative totals at the bottom, and the least negative at the top. If you did it right, it should look like this.

Then, if you want to do the same for the top scores, just repeat the same process for the Top scores.

----

And that's everything. Now we can copy-past the entire sheet into a new one and do the same for the overall totals...wait, why are you walking away? No, don't leave yet, I still have to tell you some stuff! Come baaack!

----
----

Anyways, there you have it. That should give you a spreadsheet that looks pretty darn close to mine. And really, you can pretty much copy-paste the spreadsheet to a new one and use that for Overall totals, though you'll have to go and get those specific totals.

As far as updating goes, you'll have to wait until the page auto-updates, which is every Saturday at Noon Eastern Time (I think; I know it updates at 1 for me, and I'm Central Time, so it's either Mountain or Eastern), then go through each board and update all the necessary totals. Most of the time you'll have to update yours, next, and top scores for each column, unless you meet a goal, and sometimes you only have to change yours if you didn't get a rank-up and the top scorer didn't increase their total.

When you're all updated, re-sort the differentials on the side, and then just choose the least negative one to work on. Rinse and repeat this until you reach your desired goals and/or get bored of doing that or GH/RB.

----

If you made it through this entire thread, congratz and thank you for reading this! Please don't hesitate to point out any errors in spelling, grammer, or if any of the images are wrong or don't match up with what you're getting. And don't hesitate to ask if anything I said didn't make sense. I do wish I had started doing this much earlier as it would've made making goals a lot easier to do and to follow, but I guess it's too late for that. Oh well.
_________________

GH accomps: FFWF, DE, Battery HDrums FCs! HDrums 46/49!!
RB accomps: RB3 MDrums FGFC! RB3 MOMBFGFC!
Old Youtube, New Youtube | I do Twitch, now!

[UltraHeroABC5]

Thanks man. Btw, congrats on 7k posts :D

btw, I just wanted to put here that you have lots of scores, wow. I think you may be able to get some more drums scores on RB3 (specifically medium) for some extra points and if you wanted
sorry if that was off topic, just thought i could've let you know x)
_________________

Walter Time on Xbox and pretty much every other platform. Vox main.

[UltraHeroABC5]

Thanks man. Btw, congrats on 7k posts :D

btw, I just wanted to put here that you have lots of scores, wow. I think you may be able to get some more drums scores on RB3 (specifically medium) for some extra points and if you wanted
sorry if that was off topic, just thought i could've let you know x)
_________________

Walter Time on Xbox and pretty much every other platform. Vox main.

[voidedalive2x]

Problem 1: The blue pad on my kit is coming off and I need to reattach it.
Problem 2: Even when I do, I still need to set myself up so I can play first, which won't happen until I get my new apartment and figure out when, if ever, I can play.

But yeah, Drums are the least played and least scored category of mine. But I'll be working on it more often than anything given how small the gaps are where I am, so don't worry.
_________________

GH accomps: FFWF, DE, Battery HDrums FCs! HDrums 46/49!!
RB accomps: RB3 MDrums FGFC! RB3 MOMBFGFC!
Old Youtube, New Youtube | I do Twitch, now!

[voidedalive2x]

Problem 1: The blue pad on my kit is coming off and I need to reattach it.
Problem 2: Even when I do, I still need to set myself up so I can play first, which won't happen until I get my new apartment and figure out when, if ever, I can play.

But yeah, Drums are the least played and least scored category of mine. But I'll be working on it more often than anything given how small the gaps are where I am, so don't worry.
_________________

GH accomps: FFWF, DE, Battery HDrums FCs! HDrums 46/49!!
RB accomps: RB3 MDrums FGFC! RB3 MOMBFGFC!
Old Youtube, New Youtube | I do Twitch, now!

[UltraHeroABC5]

ahh i see. good luck with that stuff. Can't wait
_________________

Walter Time on Xbox and pretty much every other platform. Vox main.

[UltraHeroABC5]

ahh i see. good luck with that stuff. Can't wait
_________________

Walter Time on Xbox and pretty much every other platform. Vox main.

[Cabanon]

massive wall of text

nice tutorial void.
_________________

The Complete GH:M Comprehensive Guide To Unlock All Stuff
The Complete GH:SH Comprehensive Guide To Unlock All Stuff
The Complete GH:VH Comprehensive Guide To Unlock All Stuff
The Complete GH:WoR Comprehensive Guide To Unlock All Stuff
Wanna Know How Many Band Moments Your Favorite Song Has ? Click Here

#TeamOldPeople

[Cabanon]

massive wall of text

nice tutorial void.
_________________

The Complete GH:M Comprehensive Guide To Unlock All Stuff
The Complete GH:SH Comprehensive Guide To Unlock All Stuff
The Complete GH:VH Comprehensive Guide To Unlock All Stuff
The Complete GH:WoR Comprehensive Guide To Unlock All Stuff
Wanna Know How Many Band Moments Your Favorite Song Has ? Click Here

#TeamOldPeople

[voidedalive2x]

[voidedalive2x]

[Mercurial]

I've included a lot of this sort of information (although before this guide) in my spreadsheet - my question to you:

How do I use these UserScore to separate by instrument AND difficulty (Easy bass for example); as well as separate GH from RB? Can I do any of that?

[Mercurial]

I've included a lot of this sort of information (although before this guide) in my spreadsheet - my question to you:

How do I use these UserScore to separate by instrument AND difficulty (Easy bass for example); as well as separate GH from RB? Can I do any of that?

[UltraHeroABC5]

I don't think so
you can only seperate by 1 category at a time (difficulty, platform, instrument, etc)
although, bushidox's guitar+bass boards are a good substitute for that
He has 8 boards on his thread(s), one for each difficulty and for each instrument (guitar + bass)
_________________

Walter Time on Xbox and pretty much every other platform. Vox main.

[UltraHeroABC5]

I don't think so
you can only seperate by 1 category at a time (difficulty, platform, instrument, etc)
although, bushidox's guitar+bass boards are a good substitute for that
He has 8 boards on his thread(s), one for each difficulty and for each instrument (guitar + bass)
_________________

Walter Time on Xbox and pretty much every other platform. Vox main.

[Mercurial]

Yeah; and I've included his rankings in my spreadsheet - I'd just like the ability to do the same for Guitar/Vox for example.

[Mercurial]

Yeah; and I've included his rankings in my spreadsheet - I'd just like the ability to do the same for Guitar/Vox for example.

[voidedalive2x]

[voidedalive2x]

[Mercurial]

I mis-typed; I meant for Drums / Vox - the areas that bushi doesn't cover :)

I wonder if DrSham might be interested in sharing some macros :P It'd be interesting to find how he pulls data from SH for bushi and see if I can code it myself.

[Mercurial]

I mis-typed; I meant for Drums / Vox - the areas that bushi doesn't cover :)

I wonder if DrSham might be interested in sharing some macros :P It'd be interesting to find how he pulls data from SH for bushi and see if I can code it myself.

[bushidox]

Very interesting thread. You provided nice step by step basic Excel tutorial steps that should allow pretty much anyone with the program the tools to setup their own comparison chart. Also, congrats on 7000 posts! Impressive stuff indeed.

Now, onto Mercurial's questions. In order to get data for every instrument, you pull that data straight from score hero's pages. If, you do, as I do, a full pull of ALL user data, you can then easily have the Excel macros pull numbers from a given difficulty + instrument by simply adding them up. How the boards that DrSham made for me work are simple, but excellent. Keep in mind, this is done using the VBASIC editor tool found in the 2007 version of Microsoft Excel, and will be different in other versions, hey the macros are old, what can you do.

1) First tab is the display tab that shows the rankings, and a pair of macros that run the data refresh.
2) Second tab is the "old" rankings that stores the data from last update for comparison sake.
3) Third tab is the Movements tab that allows me to easily see the exact score from the last time that the boards were updated to the new current data, a simple subtraction as it were.
4) Fourth tab is the Data tab that actually has a lot going on. You have All Rank, All Total, Total Rank, Total Total, User and then a column for each game breakdown.
All Rank takes a look at every value in the All Total column and assigns each one a score out of 500 (you can make it be more that's just how many I pull for convenience and time). This is done by a simple macro that just checks the value in the column against every other value with the equation: =RANK(C2,$C$2:$C$500,0)
Rank literally just supplies a ranking 1 through z, C2 is the first cell of the data column that has the scores, the $ are absolute addressifies that tell it where to start, then the colon is the range, C500 is how many values you want it to check through, finally, 0 is where you want the ranking to start.
All Total on the other hand is a sum of all the columns of games that you pull data from for this particular user.
Total Rank and Total Total are for the Grand total columns for users and games.
User is where MAGIC happens. In this column you type in the exact spelling and capitialization of a given name that is stored in ScoreHero's external data, if you mess up even a single character there is no match and you get nada. If, instead this name matches a given user, you get data to magically fill in for each game to the right.
For the game columns you have a fairly powerful and complex tool called Vlookup, which looks for the exact Variable = V that you type, in this case the name. The macro we use is:
=IFERROR(VLOOKUP($F2,GHWT!$B$2:$M$20000,2,FALSE),0)+IFERROR(VLOOKUP($F2&" ",GHWT!$B$2:$M$20000,2,FALSE),0)
That looks scary, but it does this, lookup name (VLOOKUP), F2 is the place where you put the variable, in this case the user name, GHWT! indicates a tab, in this case GHWT tab, as we have a tab for each game used starting with our 5th tab. Once again the $B$2 is an address, in this case the cell B2 to start the lookup, M20000 is the extent of how far you want to scan data, aka 20,000 unique user rankings, 2 indicates the number of possible choices, and false tells it what to display if no answer is found, aka a wrong user name.
You repeat this for each game to the right, having a matching tab for pulling external data for each game and ranking.

Now, onto the game tabs, the part that will likely answer your question the best so far. Starting with the 5th tab, we have a different tab per each game. On each tab there is a simple external connection. In 2007's case, you go to the Data tab across the top, select connection, create one with a name (I would call it the same as the tab = game for sanity's sake) and then go into the properties. Under the properties, you want to click on the Definition tab, and under the field for Connection string you want the following:
http://www.scorehero.com/rankings.php?group=7&game=0&size=1&inst1=2&inst=2&diff=1&tier=2&page=0
What this does is the following, it creates an external link, that has to be a table with plain text, which, conveniently Score Hero has. Now, group = GH/RB/DJ etc. Game = exact game that you want, 0 is for WT. Size in this case should always be 1, its formatting for the site. inst1 and inst are both for bass in this case , or instrument 2. Difficulty is 1-4, in this case 1 for Easy. Tier in this case is for all, you can select specific game tiers in some tables on the site. Page is especially important, if set to 1, you only pull up to 50 or 100 scores, but 0 pulls ALL of the scores, be careful, as say on GH3 Expert for some songs there are 10,000+ scores.
In other words you can read that as get me the external data table that matches GH, GHWT, bass, bass, easy, all, all scores.

When the tables are refreshed, it simply looks up this query string for each game, and voila new data. However, this only works per instrument PER difficulty, you would have to setup a different query for each one.

I hope that helps in some way.
_________________

The Unofficial Score Hero GH Guitar leaderboard for X/H/M/E: http://www.scorehero.com/forum/viewtopic.php?p=1638203#1638203
The Unofficial Score Hero RB Guitar leaderboard for X/H/M/E: http://rockband.scorehero.com/forum/viewtopic.php?p=703852#703852
The Unofficial Score Hero Bass GH leaderboard for X/H/M/E: http://www.scorehero.com/forum/viewtopic.php?t=107740
The Unofficial Score Hero Bass RB leaderboard for X/H/M/E: http://rockband.scorehero.com/forum/viewtopic.php?p=703842#703842
The Unofficial Cross Platform FGFC leaderboard for X/H/M/E: http://www.scorehero.com/forum/viewtopic.php?t=108548

[bushidox]

Very interesting thread. You provided nice step by step basic Excel tutorial steps that should allow pretty much anyone with the program the tools to setup their own comparison chart. Also, congrats on 7000 posts! Impressive stuff indeed.

Now, onto Mercurial's questions. In order to get data for every instrument, you pull that data straight from score hero's pages. If, you do, as I do, a full pull of ALL user data, you can then easily have the Excel macros pull numbers from a given difficulty + instrument by simply adding them up. How the boards that DrSham made for me work are simple, but excellent. Keep in mind, this is done using the VBASIC editor tool found in the 2007 version of Microsoft Excel, and will be different in other versions, hey the macros are old, what can you do.

1) First tab is the display tab that shows the rankings, and a pair of macros that run the data refresh.
2) Second tab is the "old" rankings that stores the data from last update for comparison sake.
3) Third tab is the Movements tab that allows me to easily see the exact score from the last time that the boards were updated to the new current data, a simple subtraction as it were.
4) Fourth tab is the Data tab that actually has a lot going on. You have All Rank, All Total, Total Rank, Total Total, User and then a column for each game breakdown.
All Rank takes a look at every value in the All Total column and assigns each one a score out of 500 (you can make it be more that's just how many I pull for convenience and time). This is done by a simple macro that just checks the value in the column against every other value with the equation: =RANK(C2,$C$2:$C$500,0)
Rank literally just supplies a ranking 1 through z, C2 is the first cell of the data column that has the scores, the $ are absolute addressifies that tell it where to start, then the colon is the range, C500 is how many values you want it to check through, finally, 0 is where you want the ranking to start.
All Total on the other hand is a sum of all the columns of games that you pull data from for this particular user.
Total Rank and Total Total are for the Grand total columns for users and games.
User is where MAGIC happens. In this column you type in the exact spelling and capitialization of a given name that is stored in ScoreHero's external data, if you mess up even a single character there is no match and you get nada. If, instead this name matches a given user, you get data to magically fill in for each game to the right.
For the game columns you have a fairly powerful and complex tool called Vlookup, which looks for the exact Variable = V that you type, in this case the name. The macro we use is:
=IFERROR(VLOOKUP($F2,GHWT!$B$2:$M$20000,2,FALSE),0)+IFERROR(VLOOKUP($F2&" ",GHWT!$B$2:$M$20000,2,FALSE),0)
That looks scary, but it does this, lookup name (VLOOKUP), F2 is the place where you put the variable, in this case the user name, GHWT! indicates a tab, in this case GHWT tab, as we have a tab for each game used starting with our 5th tab. Once again the $B$2 is an address, in this case the cell B2 to start the lookup, M20000 is the extent of how far you want to scan data, aka 20,000 unique user rankings, 2 indicates the number of possible choices, and false tells it what to display if no answer is found, aka a wrong user name.
You repeat this for each game to the right, having a matching tab for pulling external data for each game and ranking.

Now, onto the game tabs, the part that will likely answer your question the best so far. Starting with the 5th tab, we have a different tab per each game. On each tab there is a simple external connection. In 2007's case, you go to the Data tab across the top, select connection, create one with a name (I would call it the same as the tab = game for sanity's sake) and then go into the properties. Under the properties, you want to click on the Definition tab, and under the field for Connection string you want the following:
http://www.scorehero.com/rankings.php?group=7&game=0&size=1&inst1=2&inst=2&diff=1&tier=2&page=0
What this does is the following, it creates an external link, that has to be a table with plain text, which, conveniently Score Hero has. Now, group = GH/RB/DJ etc. Game = exact game that you want, 0 is for WT. Size in this case should always be 1, its formatting for the site. inst1 and inst are both for bass in this case , or instrument 2. Difficulty is 1-4, in this case 1 for Easy. Tier in this case is for all, you can select specific game tiers in some tables on the site. Page is especially important, if set to 1, you only pull up to 50 or 100 scores, but 0 pulls ALL of the scores, be careful, as say on GH3 Expert for some songs there are 10,000+ scores.
In other words you can read that as get me the external data table that matches GH, GHWT, bass, bass, easy, all, all scores.

When the tables are refreshed, it simply looks up this query string for each game, and voila new data. However, this only works per instrument PER difficulty, you would have to setup a different query for each one.

I hope that helps in some way.
_________________

The Unofficial Score Hero GH Guitar leaderboard for X/H/M/E: http://www.scorehero.com/forum/viewtopic.php?p=1638203#1638203
The Unofficial Score Hero RB Guitar leaderboard for X/H/M/E: http://rockband.scorehero.com/forum/viewtopic.php?p=703852#703852
The Unofficial Score Hero Bass GH leaderboard for X/H/M/E: http://www.scorehero.com/forum/viewtopic.php?t=107740
The Unofficial Score Hero Bass RB leaderboard for X/H/M/E: http://rockband.scorehero.com/forum/viewtopic.php?p=703842#703842
The Unofficial Cross Platform FGFC leaderboard for X/H/M/E: http://www.scorehero.com/forum/viewtopic.php?t=108548

[Mercurial]

Hi bushi - thanks for the walkthrough. Thankfully; I already know how to use excel more than well enough to carry this out. The only piece of the puzzle I need to do is find a way to pull the data from SH :P I'll take another look later tonight.

[Mercurial]

Hi bushi - thanks for the walkthrough. Thankfully; I already know how to use excel more than well enough to carry this out. The only piece of the puzzle I need to do is find a way to pull the data from SH :P I'll take another look later tonight.

[voidedalive2x]

I'd actually kinda like to see what some of your spreadsheets look like, Ultrahero, mercurial. Both to see if you did it all right, and what kind of things you're shooting for.
_________________

GH accomps: FFWF, DE, Battery HDrums FCs! HDrums 46/49!!
RB accomps: RB3 MDrums FGFC! RB3 MOMBFGFC!
Old Youtube, New Youtube | I do Twitch, now!

[voidedalive2x]

I'd actually kinda like to see what some of your spreadsheets look like, Ultrahero, mercurial. Both to see if you did it all right, and what kind of things you're shooting for.
_________________

GH accomps: FFWF, DE, Battery HDrums FCs! HDrums 46/49!!
RB accomps: RB3 MDrums FGFC! RB3 MOMBFGFC!
Old Youtube, New Youtube | I do Twitch, now!

[Mercurial]

Hi voided. There's about 15 hidden sheets that I haven't finished working on yet, but I've shared my spreadsheet for you here:

Linky

Should be fairly simple to work out how it works. The FCs are calculated automatically from note streak. GH3 Career scores are not yet complete and I've not finished adding note streaks to everything in the later games, so there's still a lot of work to do. The intention was to build this and make it shareable.

Up the top (frozen) you'll see the rankings that I get from bushi's spreadsheets; although I haven't finished that either, just for guitar. I left the calculation sheet for that unhidden so that you can take a look if you like.

All the formatting is automatic and all of the calculation is automatic. Once I'm finished, I intend on adding RB, as well as all of the DLC (those are in the hidden sheets already but not as thorough as what I've shared to you)

[Mercurial]

Hi voided. There's about 15 hidden sheets that I haven't finished working on yet, but I've shared my spreadsheet for you here:

Linky

Should be fairly simple to work out how it works. The FCs are calculated automatically from note streak. GH3 Career scores are not yet complete and I've not finished adding note streaks to everything in the later games, so there's still a lot of work to do. The intention was to build this and make it shareable.

Up the top (frozen) you'll see the rankings that I get from bushi's spreadsheets; although I haven't finished that either, just for guitar. I left the calculation sheet for that unhidden so that you can take a look if you like.

All the formatting is automatic and all of the calculation is automatic. Once I'm finished, I intend on adding RB, as well as all of the DLC (those are in the hidden sheets already but not as thorough as what I've shared to you)