MTG Commander/Elder Dragon Highlander http://mtgcommander.net/Forum/ 

For Spreadsheet Junkies http://mtgcommander.net/Forum/viewtopic.php?f=14&t=4328 
Page 1 of 1 
Author:  vorketh [ 2010Jul22 3:08 pm ] 
Post subject:  For Spreadsheet Junkies 
Hello all, New to the forums here  in fact, brand new to EDH. Since I wanted to make a spreadsheet for my decklist that I'm making that contains lots of information on the cards, I figured I would start my forum posting off by providing something useful. Here's an Excel Formula (made in Excel 2007) that reads a mana cost in a cell (referenced as F15 throughout this formula) that is entered in standard format (order of letters don't matter): e.g: UU, 1UU, WUBRG, WBRGU (this is nonstandard order, but is still accepted), 3WBRGU (same here), 5WWUBBRGG you get the point. =IF(LEN(F15)=1,IF(IFERROR(ISNUMBER(F15),0),F15,1),IF(IFERROR(FIND("XX",F15,1),0),IF(ISNUMBER((TEXT(MID(F15,3,1),"0")*1)),LEN(MID(F15,4,LEN(F15)3))+TEXT(MID(F15,3,1),"0"),LEN(MID(F15,3,LEN(F15)2))),IF(LEFT(F15,1)="X",IF(ISNUMBER((TEXT(MID(F15,2,1),"0")*1)),MID(F15,2,1)+(LEN(MID(F15,3,LEN(F15)2))),LEN(MID(F15,2,LEN(F15)1))),IF(IFERROR(ISNUMBER(LEFT(F15,2)*1),0),LEFT(F15,2)+LEN(MID(F15,3,LEN(F15)2)),IF(IFERROR(ISNUMBER(LEFT(F15,1)*1),0),LEN(MID(F15,2,LEN(F15)1))+TEXT(LEFT(F15,1),"0"),LEN(F15)))))) Now off to look for deck ideas! Edit: Changed the formula to capture values including X and equal to 0 properly. Edit2: Changed to accommodate 2digit numbers in casting costs. Edit3: Changed to accommodate XX costs. Hint  to avoid having to edit the formula, just paste the formula above into cell G15 and it will already be pointing to its neighbor on the left (then copy cells from there). Or however your stuff is oriented. 
Author:  Daf [ 2010Jul22 5:34 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
What if it's Autochthon Wurm, Darksteel Colossus, etc? 
Author:  vorketh [ 2010Jul22 5:52 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
Then you use the slightly longer adjusted formula Thanks Daf. 
Author:  Daf [ 2010Jul22 6:06 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
What about Gleemax Aww, the new formula doesn't like x spells 
Author:  vorketh [ 2010Jul22 7:02 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
It handles X just fine (so long as X is at the beginning), but you did just make me realize that X is not always alone... XX, X2, etc. 
Author:  Daf [ 2010Jul22 7:21 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
Oh, yes, I was checking x2r and x1b and it was coming up one short; I hadn't checked xr, etc. 
Author:  vorketh [ 2010Jul22 8:08 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
Ok... I think I got it this time. Tested pretty exhaustively, so hopefully all normal circumstances are working. No, 1,000,000 colorless doesn't work  I can live with that. Furthermore, since I'm not aware of any card that has X## (2 digit colorless AND an X), it doesn't function with that. Also, I'm not aware of any XXX cards, so that's out too. Everything else should be fair game. I kinda cheated  in shrinking the formula for elegance, I broke single color and zero costs, so I just tagged those to the outside since they were easy 
Author:  Daf [ 2010Jul22 8:38 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
vorketh wrote: No, 1,000,000 colorless doesn't work  I can live with that. You could do it as =if(isnumber(A1),A1,[rest of formula for XnWUBRG]). Works for "0"costs but not " "costs (but they work with =len(A1) anyway). I tried finding a way to strip a numeric substring out of an alphanumeric string, which should let you go CMC = (X) (numberic substring)*1 + Len(rest of string) but I have actual work to do today (:() so I can't get to it right now. My other though was to do it as something like left(A1,min(search("w"),search("u"),...)1) but it errors out if it doesn't find any given one (so it only works for 5coloured) :/ I could code around that and set the missing colours to len+1 but it starts to get really long: 
Author:  vorketh [ 2010Jul23 3:18 am ] 
Post subject:  Re: For Spreadsheet Junkies 
As you say, catching the million cost exception is as easy as adding a condition for it on the outside  that's the best way to handle exceptions. The rest of your strategy is exactly what I have used. The way to solve your search errors is to use IFERROR arround the search term. Bear in mind that the reason I mentioned the version of Excel I made it in is that IFERROR only exists in Office 2007 and later. 
Author:  Pirengle [ 2010Jul23 6:40 am ] 
Post subject:  Re: For Spreadsheet Junkies 
This is pretty awesome. I've paid more attention to mana type in my database than mana amount, but I suppose being able to limit by casting cost could come in handy. Mind if I use this? 
Author:  vorketh [ 2010Jul23 2:54 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
I'll do one better. I finished my spreadsheet  you can all have it: http://www.filefront.com/17140366/EDHTresserhorn.xlsx It's also my first decklist  obviously I paid more attention to the spreadsheet than the deck, but we'll see where that goes. Basically, the blue table is where you enter information concerning the cards (don't forget your general right above the table). The summary stats at the top will dynamically change (but you need to enter the target land qty for your preference), you can use the filters / sorts in the table to your liking, and the mana curve (it's not a "real" one, it's just a line histogram of the CMCs). Enjoy 
Author:  Pirengle [ 2010Jul24 11:54 am ] 
Post subject:  Re: For Spreadsheet Junkies 
vorketh wrote: The summary stats at the top will dynamically change (but you need to enter the target land qty for your preference), you can use the filters / sorts in the table to your liking, and the mana curve (it's not a "real" one, it's just a line histogram of the CMCs). I changed the line graph to a pie chartthey're easier for me to read at a glance. This is nifty. Thanks for sharing this. 
Author:  vorketh [ 2010Jul26 5:04 pm ] 
Post subject:  Re: For Spreadsheet Junkies 
Absolutely not a problem. It's satisfying to see my work enjoyed. Wish I got that AT work j/k 
Page 1 of 1  All times are UTC  7 hours 
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ 