Advanced FFR Stats Made Easy

**musicdemon** · 08-29-2009, 11:12 AM

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?

**MrMagic5239** · 08-29-2009, 11:13 AM

looks sweet

Maybe if Tass likes this idea he could implement it into the website

**insanefreddy926** · 08-29-2009, 11:20 AM

wowowowooooooowww that is sick

**i love you** · 08-29-2009, 11:22 AM

bob bob · 08-29-2009, 11:45 AM

that looks awesome

**_REDYOSHI_** · 08-29-2009, 09:09 PM

Sweet! Awesome job putting that together.

**tofurox** · 08-29-2009, 09:23 PM

**foilman8805** · 08-29-2009, 09:27 PM

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.

**tofurox** · 08-29-2009, 09:32 PM

It won't split into columns like you said it would. Unless I'm missing something....

yeah, same as above^^^^

cedolad · 08-29-2009, 09:33 PM

**Vanilla Mnm** · 08-29-2009, 09:36 PM

Holy sh**. Nice job musicdemon, this looks insane. Hope FFR goes further with this.

**musicdemon** · 08-29-2009, 09:42 PM

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.

**bubblechamber** · 08-29-2009, 09:43 PM

Quote:

Originally Posted by tofurox

It won't split into columns like you said it would. Unless I'm missing something....

yeah, same as above^^^^

You can skip the text file and copy your levelranks directly into the spreadsheet. It'll look weird and the song titles will be links, but it works. Hopefully there's a better way.

Edit: ninja'd. Also, +1

**tofurox** · 08-29-2009, 09:47 PM

Yes, but now it seperates each word into a seperate column. So Excite Bike is a 2 column name now.... etc.

**musicdemon** · 08-29-2009, 09:47 PM

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

tj28 · 08-29-2009, 09:52 PM

Quote:

Originally Posted by musicdemon

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

It works.
Paste Special->Text only.

**tofurox** · 08-29-2009, 09:52 PM

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....

**tofurox** · 08-29-2009, 10:04 PM

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.

**tofurox** · 08-29-2009, 10:20 PM

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!

**musicdemon** · 08-29-2009, 10:25 PM

If you've got Excel 2007, click the Normal button that you see in this screenshot to remove the hyperlinks:

08-29-2009, 11:12 AM	#1
musicdemon 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? __________________ Winner of Wargasm's 1 Billion Score Challenge* 3rd place in badman's 2nd tourney of darkness (beginner's division)

08-29-2009, 11:13 AM	#2
MrMagic5239 FFR Veteran Join Date: Feb 2007 Age: 34 Posts: 4,096	Re: Advanced FFR Stats Made Easy looks sweet Maybe if Tass likes this idea he could implement it into the website __________________ ~Grand Chase Stats~ Elesis (Knight/Spearman/Sword Master/ Savior) Lvl 80/80, MP Bars 4/4 EXP Rank 1 Lire (Archer/Crossbowmen/Arch Ranger/Nova) Lvl 80/80, MP Bars 4/4, EXP Rank 1 Arme (Mage/Alchemist/Warlock/Battle Mage) Lvl 80/80 MP Bars 4/4, EXP Rank 1 Ronan (Spell Knight/Dragon Knight/Aegis Knight/Abyss Knight) Lvl 80/80, MP Bars 4/4, EXP Rank 1 Lass (Thief/Assassin/Dark Assassin/Striper) Lvl 80/80, MP Bars 4, EXP Rank 1 Ryan (Druid/Sentinel/Viken/Xenocider) Lvl 80/80, MP Bars 4/4, EXP Rank 1 Amy (Dancer/Musician/Siren/Superstar) Lvl 78/80, MP Bars 4/4, EXP Rank 13 Sieghart (Gladiator/Warlord/Duelist/Prime Knight) Lvl 80/80, MP Bars 4/4, EXP Rank 1 Jin (Fighter/Shisa/Asura/Rama) Lvl 80/80, MP Bars 4/4 EXP Rank 1 Zero (Seeker/Wanderer) Lvl 79/80, MP Bars 4/4 EXP Rank 9 Dio (Stygian/Drakar//Leviathan) Lvl 80/80, MP Bars 4/4, EXP Rank 1 Rin (Caller) Lvl 77/80, MP Bars 4/4, EXP Rank 32 Rufus (Bounty Hunter/Killer/Ravager/Arbiter) Lvl 80/80, MP Bars 4/4, EXP Rank 1 Ley (Summoner/Harbringer) lvl 79/80, MP Bars 4/4, EXP Rank 21 Asin (Desciple) Lvl 80/80, MP Bars 4/4, EXP Rank 1 (Eastern Rain Liquid Jade aquired)

08-29-2009, 11:20 AM	#3
insanefreddy926 Super Member Join Date: Feb 2005 Location: the Andromeda galaxy Age: 32 Posts: 187	Re: Advanced FFR Stats Made Easy wowowowooooooowww that is sick __________________ yeaorwgh.

08-29-2009, 11:22 AM	#4
i love you 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
bob bob This Is How We Do It~ Join Date: Oct 2006 Location: Portsmouth,United Kingdom Age: 28 Posts: 3,226	Re: Advanced FFR Stats Made Easy that looks awesome __________________ Best AAA's: NAINAI69, Eradication, Since 1983 Best Non AAA's: Gunkienen Tarusuigin, (1-0-1-1), Pandora (6-0-1-0) Rotary Slicers MISSFLAG Waiting for 2 Million members... 5th Place: 7th Official Tournament D5

08-29-2009, 09:09 PM	#6
_REDYOSHI_ 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
tofurox Them arrows. Join Date: May 2006 Location: Allen, Texas Age: 30 Posts: 2,263	Re: Advanced FFR Stats Made Easy WOOH __________________ This sig has been enhanced by the FFR Staff for being too large. Thank you for participating.

08-29-2009, 09:27 PM	#8
foilman8805 smoke wheat hail satin Join Date: Sep 2006 Location: LA baby Age: 35 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
tofurox Them arrows. Join Date: May 2006 Location: Allen, Texas Age: 30 Posts: 2,263	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^^^^ __________________ This sig has been enhanced by the FFR Staff for being too large. Thank you for participating.

08-29-2009, 09:33 PM	#10
cedolad moonchild~ Join Date: Jan 2007 Location: . Age: 30 Posts: 6,879	Re: Advanced FFR Stats Made Easy +1 __________________

08-29-2009, 09:36 PM	#11
Vanilla Mnm 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	#12
musicdemon 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. __________________ Winner of Wargasm's 1 Billion Score Challenge 3rd place in badman's 2nd tourney of darkness (beginner's division)

08-29-2009, 09:47 PM	#14
tofurox Them arrows. Join Date: May 2006 Location: Allen, Texas Age: 30 Posts: 2,263	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. __________________ This sig has been enhanced by the FFR Staff for being too large. Thank you for participating.

08-29-2009, 09:47 PM	#15
musicdemon 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 __________________ Winner of Wargasm's 1 Billion Score Challenge 3rd place in badman's 2nd tourney of darkness (beginner's division)

08-29-2009, 09:52 PM	#17
tofurox Them arrows. Join Date: May 2006 Location: Allen, Texas Age: 30 Posts: 2,263	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.... __________________ This sig has been enhanced by the FFR Staff for being too large. Thank you for participating.

08-29-2009, 10:04 PM	#18
tofurox Them arrows. Join Date: May 2006 Location: Allen, Texas Age: 30 Posts: 2,263	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
tofurox Them arrows. Join Date: May 2006 Location: Allen, Texas Age: 30 Posts: 2,263	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
musicdemon 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: __________________ Winner of Wargasm's 1 Billion Score Challenge 3rd place in badman's 2nd tourney of darkness (beginner's division)

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)