Login | Register


All times are UTC - 7 hours


It is currently 2017-Aug-19 11:29 pm




Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 3:08 pm 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
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.


Last edited by vorketh on 2010-Jul-22 8:10 pm, edited 3 times in total.

Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 5:34 pm 
User avatar

Joined: 2009-Oct-18 8:52 pm
Age: Elder Dragon
Location: Oz
What if it's Autochthon Wurm, Darksteel Colossus, etc?

_________________
Kalterwolf wrote:
Shatter and Bloodshot Trainee may be confirmed via Orb of Insight, but dont quote me on it.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 5:52 pm 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
Then you use the slightly longer adjusted formula :P

Thanks Daf.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 6:06 pm 
User avatar

Joined: 2009-Oct-18 8:52 pm
Age: Elder Dragon
Location: Oz
What about Gleemax ;)

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

_________________
Kalterwolf wrote:
Shatter and Bloodshot Trainee may be confirmed via Orb of Insight, but dont quote me on it.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 7:02 pm 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
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.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 7:21 pm 
User avatar

Joined: 2009-Oct-18 8:52 pm
Age: Elder Dragon
Location: Oz
Oh, yes, I was checking x2r and x1b and it was coming up one short; I hadn't checked xr, etc.

_________________
Kalterwolf wrote:
Shatter and Bloodshot Trainee may be confirmed via Orb of Insight, but dont quote me on it.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 8:08 pm 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
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


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-22 8:38 pm 
User avatar

Joined: 2009-Oct-18 8:52 pm
Age: Elder Dragon
Location: Oz
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))

_________________
Kalterwolf wrote:
Shatter and Bloodshot Trainee may be confirmed via Orb of Insight, but dont quote me on it.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-23 3:18 am 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
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.


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-23 6:40 am 

Joined: 2009-Oct-17 1:34 pm
Age: Drake
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?

_________________
Food makes EDH better. All the meyvns agree. 8)

Current decks:
Jaya Ballard, Task Mage: I'll deck you with red. C'mon and try me.
Wort, Boggart Auntie: Less discard, more recursion
Darien, King of Kjeldor: are you really sure you want to do that?
Experiment Kraj: Counterculture!
Jhoira of the Ghitu: About as Izzet as Izzet gets.
Oros, the Avenger: Raiders of the Lost Snark


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-23 2:54 pm 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
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


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-24 11:54 am 

Joined: 2009-Oct-17 1:34 pm
Age: Drake
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.

_________________
Food makes EDH better. All the meyvns agree. 8)

Current decks:
Jaya Ballard, Task Mage: I'll deck you with red. C'mon and try me.
Wort, Boggart Auntie: Less discard, more recursion
Darien, King of Kjeldor: are you really sure you want to do that?
Experiment Kraj: Counterculture!
Jhoira of the Ghitu: About as Izzet as Izzet gets.
Oros, the Avenger: Raiders of the Lost Snark


Top
 Offline Profile  
 
 Post subject: Re: For Spreadsheet Junkies
AgePosted: 2010-Jul-26 5:04 pm 

Joined: 2010-Jul-22 3:04 pm
Age: Hatchling
Absolutely not a problem. It's satisfying to see my work enjoyed. Wish I got that AT work :P j/k


Top
 Offline Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 7 hours


Who is online

Users browsing this forum: No registered users and 32 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to: