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 [ 2010-Jul-22 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 non-standard 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 2-digit 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 [ 2010-Jul-22 5:34 pm ]
Post subject:  Re: For Spreadsheet Junkies

What if it's Autochthon Wurm, Darksteel Colossus, etc?

Author:  vorketh [ 2010-Jul-22 5:52 pm ]
Post subject:  Re: For Spreadsheet Junkies

Then you use the slightly longer adjusted formula :P

Thanks Daf.

Author:  Daf [ 2010-Jul-22 6:06 pm ]
Post subject:  Re: For Spreadsheet Junkies

What about Gleemax ;)

Aww, the new formula doesn't like x spells :(

Author:  vorketh [ 2010-Jul-22 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 [ 2010-Jul-22 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 [ 2010-Jul-22 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 :P

Author:  Daf [ 2010-Jul-22 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 5-coloured) :/
I could code around that and set the missing colours to len+1 but it starts to get really long:
=MIN(IFERROR(SEARCH("w",A1),LEN(A1)+1),IFERROR(SEARCH("u",A1),LEN(A1)+1),IFERROR(SEARCH("b",A1),LEN(A1)+1),IFERROR(SEARCH("g",A1),LEN(A1)+1),IFERROR(SEARCH("r",A1),LEN(A1)+1))

Author:  vorketh [ 2010-Jul-23 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 [ 2010-Jul-23 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 [ 2010-Jul-23 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/EDH-Tresserhorn.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 [ 2010-Jul-24 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 chart--they're easier for me to read at a glance.

This is nifty. Thanks for sharing this.

Author:  vorketh [ 2010-Jul-26 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 :P 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/