|
08-29-2009, 11:12 AM | #1 |
FFR Veteran
Join Date: Dec 2008
Location: Massachusetts
Age: 36
Posts: 274
|
Advanced FFR Stats Made Easy
I've created a formula in Microsoft Excel that will calculate your AAAs, FCs, Blackflags (BFs), Booflags (Boo), and Single Digit Goods (SDGs) automatically. It's quite useful for stat calculation, and you don't have to recalculate anything if you add/remove any song from the list.
Estimated Time to Completion: 3 minutes Here's what you do: 1) If you're a subscriber, simply copy and paste your levelranks into a txt file and save it. It should look something like this: If you aren't, use one of my excel files as a template for your scores or get a subscriber to help you out. My "Basic" Excel file is just like the one in this tutorial. The regular Excel file is the one I use, and includes columns for both the Date and Actual Difficulty. I've filled in all Advanced Difficulties from the original post of jimerax's thread. 2) Drag the text file onto your spreadsheet. It should automatically take care of all formatting and you'll see your data separated into 11 columns, 'A' through 'K'. If you somehow get the Text Import Wizard, just go with the defaults. 3) Delete the row with Token #9 (If you have it). 4) Right-click on column 'E' and choose 'Insert' to put a new column between your Score and Perfects columns and name it whatever you choose (I chose 'Status'). 5) Copy the formula below. 6) Paste the formula into the first cell of your Status column (E2). 7) Copy that cell, select all other cells in the column, and paste it. Formula: =IF(D2=0,"",IF(AND(A2=1,F2=K2,SUM(G2:J2)=0),"AAA",IF(AND(F2=K2,SUM(G2:I2)=0,J2=1),"Boo",IF(AND(F2=K2-1,SUM(H2:J2)=0,G2=1),"BF",IF(AND(SUM(H2:J2)=0,G2>1,G2<10),"SDG",IF(AND(SUM(F2:H2)=K2, I2=0,K2>31),"FC", "")))))) Why Do All Of This? The answer is simple. This is an easy way to count how many of each AAA, SDG, etc. you have without hunting through the list manually. Copy these formulas and paste them into a cell of your choice to obtain a count of each status: For AAAs: =COUNTIF(E:E, "AAA") For SDGs: =COUNTIF(E:E, "SDG") For Blackflags: =COUNTIF(E:E, "BF") For Booflags: =COUNTIF(E:E, "Boo") All songs: =COUNT(A:A) Not FC'd %: =(N9/N7)/10 (See screenshot below) Here's what mine looks like sorted By Status: EXTRA CREDIT: If you stopped right now, the sorting will be pretty messed up because of those asterisks on the FC'd songs. Let's fix that, shall we? 1) Hit Ctrl+F and you should see the Find and Replace box. 2) Click the 'Replace' tab and type: ~* in the 'Find what' input box (the ~ symbol should be right below your Esc key). 3) Put whatever you want (it can be nothing at all) to replace that asterisk in the 'Replace with' input box and hit the 'Replace All' button. If you didn't put anything in the 'Replace with' field, all of your scores will now sort properly. Community: Is there a better way to find out if the user has a FC other than specifying that "Misses = 0" when the score is > 0? This is slightly flawed, because a score could show up as FC even if the song wasn't fully completed and Autofail was on...but who does that? |
08-29-2009, 11:13 AM | #2 |
FFR Veteran
|
Re: Advanced FFR Stats Made Easy
looks sweet
Maybe if Tass likes this idea he could implement it into the website
__________________
~Grand Chase Stats~ |
08-29-2009, 11:20 AM | #3 |
Super Member
|
Re: Advanced FFR Stats Made Easy
wowowowooooooowww that is sick
__________________
yeaorwgh. |
08-29-2009, 11:22 AM | #4 |
Live a wonderful life~
Join Date: Oct 2006
Posts: 7,313
|
Re: Advanced FFR Stats Made Easy
+1
__________________
=============================== The idea that RDCP 3 may come out in the future is a fun thought to have~ =============================== |
08-29-2009, 11:45 AM | #5 |
This Is How We Do It~
|
Re: Advanced FFR Stats Made Easy
that looks awesome
|
08-29-2009, 09:09 PM | #6 |
FFR Veteran
Join Date: Dec 2008
Age: 32
Posts: 65
|
Re: Advanced FFR Stats Made Easy
Sweet! Awesome job putting that together.
|
08-29-2009, 09:23 PM | #7 |
Them arrows.
|
Re: Advanced FFR Stats Made Easy
WOOH
|
08-29-2009, 09:27 PM | #8 |
smoke wheat hail satin
Join Date: Sep 2006
Location: LA baby
Age: 36
Posts: 5,704
|
Re: Advanced FFR Stats Made Easy
Good to see you took to my initial idea we talked about on Kong and then made it absolutely incredible. Looks really awesome, music. Nice work.
Last edited by foilman8805; 08-29-2009 at 09:30 PM.. |
08-29-2009, 09:32 PM | #9 |
Them arrows.
|
Re: Advanced FFR Stats Made Easy
It won't split into columns like you said it would. Unless I'm missing something....
yeah, same as above^^^^ |
08-29-2009, 09:43 PM | #10 | |
FFR Veteran
Join Date: Apr 2009
Age: 38
Posts: 367
|
Re: Advanced FFR Stats Made Easy
Quote:
Edit: ninja'd. Also, +1 |
|
08-29-2009, 09:33 PM | #11 |
moonchild~
|
Re: Advanced FFR Stats Made Easy
+1
__________________
|
08-29-2009, 09:36 PM | #12 |
cavs
Join Date: Dec 2007
Location: Saratoga, New York
Posts: 3,258
|
Re: Advanced FFR Stats Made Easy
Holy sh**. Nice job musicdemon, this looks insane. Hope FFR goes further with this.
|
08-29-2009, 09:42 PM | #13 |
FFR Veteran
Join Date: Dec 2008
Location: Massachusetts
Age: 36
Posts: 274
|
Re: Advanced FFR Stats Made Easy
Thanks for the support, guys!
If the drag and drop method isn't working, use the regular "Open" button to open a file. Navigate to where the text file is located and change the dropdown menu in the lower-right from "All Excel Files" to "All Files". Open your text file. You should get the "Text Import" wizard. Press next on Step 1. In Step 2, the checkbox for Tab in the 'Delimiters' section should be checked. Move on to Step 3, and make sure your columns match up the right way by using the preview. |
08-29-2009, 09:47 PM | #14 |
Them arrows.
|
Re: Advanced FFR Stats Made Easy
Yes, but now it seperates each word into a seperate column. So Excite Bike is a 2 column name now.... etc.
|
08-29-2009, 09:47 PM | #15 |
FFR Veteran
Join Date: Dec 2008
Location: Massachusetts
Age: 36
Posts: 274
|
Re: Advanced FFR Stats Made Easy
I'm surprised that the drag 'n drop method didn't work. It worked like a charm using Vista + Excel 2007 and also with XP + Excel 2003. It must be that you copied the data a little differently. Don't worry, The Text Import Wizard (see above post) should sort that out for you
|
08-29-2009, 09:52 PM | #16 | |
FFR Player
Join Date: Nov 2007
Posts: 201
|
Re: Advanced FFR Stats Made Easy
Quote:
Paste Special->Text only. |
|
08-29-2009, 09:52 PM | #17 |
Them arrows.
|
Re: Advanced FFR Stats Made Easy
Copy it directly from your level ranks. Then make the row height 20. And you got it. But wait, how do you remove all the hyperlinks....
|
08-29-2009, 10:04 PM | #18 |
Them arrows.
|
Re: Advanced FFR Stats Made Easy
I can fix it. Just copy straight from the level ranks. If the row heights are messed up, make them a uniform size, like 15. Then press alt+f11. Then ctr+g. And enter "selection.hyperlinks.delete" no quotes. And to fix the color, press ctr+a and then right click, and format. Then under patterns, make it white.
__________________
This sig has been enhanced by the FFR Staff for being too large. Thank you for participating. Last edited by tofurox; 08-29-2009 at 10:11 PM.. |
08-29-2009, 10:20 PM | #19 |
Them arrows.
|
Re: Advanced FFR Stats Made Easy
Ctr+A. Then go to Format> Row height. Make the row height 15. Next make sure the width of the columns is ok so everything appears on one line. Do that for your score, and for the level name. Just highlight the entire column and drag it larger. Then, with everything highlighted press Alr+F11. Then Ctr+G. And type in "selection.hyperlinks.delete" no quotes. Then highlight all again, and right click. Then click Format Cells> Patterns. And choose the color white. Got it? It's really basic, I just suck at explaining. If you get stuck anywhere tell me. It should look normal afterwords. Then continue following his steps.
WORKS 100% now! THANK YOU SO MUCH!
__________________
This sig has been enhanced by the FFR Staff for being too large. Thank you for participating. Last edited by tofurox; 08-29-2009 at 10:30 PM.. |
08-29-2009, 10:25 PM | #20 |
FFR Veteran
Join Date: Dec 2008
Location: Massachusetts
Age: 36
Posts: 274
|
Re: Advanced FFR Stats Made Easy
If you've got Excel 2007, click the Normal button that you see in this screenshot to remove the hyperlinks:
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
|
|