1. Welcome to Smogon! Check out the Smogon Info Hub for everything you need to know about starting out in the community. Don't forget to introduce yourself in the Introduction Thread, too!
2. Welcome to Smogon Forums! Please take a minute to read the rules.
3. Ever find yourself missing out on the latest Smogon articles? We've now got a subscription service, so the newest articles (of your choice) are delivered right to your inbox! Check it out here. While you're at it, help us pick a new name!

# Programming Pokemon Resource (for DP; made in Excel)

Discussion in 'Technical Projects' started by CardsOfTheHeart, Mar 14, 2008.

1. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
ATTENTION! UPDATE FOR PLATINUM ON PAGE 3!

Today, I bring to you a group of useful Pokemon utilities made in Microsoft Excel. I have named this the "Pokemon Resource" because I have hopes of it becoming the ultimate Pokemon resource that no aspiring Trainer should be without.

This started as a pet project of mine. I was (and still am) not very good at recalling specific numbers in the heat of battle. I wanted to make something that would make it so that I wouldn't ever have to memorize particular numbers.

It all started with a table of Base Stats. I thought it would be a great idea to have the stats of all Pokemon at my fingertips. I was also compelled to allow customizable spreads for each Pokemon and organize those stats as I wished. For a long time in its development, this table also had columns for Dragontamer's Attack and Defense Tiers. I may decide to put those back in for a future release.

Then, I wanted a Stat calculator. Yes, the table of Base Stats could calculate stats, but that table is large and cumbersome for such a task. I wanted to be able to look at the stats of one Pokemon at a time. That's what the Stat Calculator enables you to do.

Finally, after deciding between several other useful functions to put in, I decided to take on the greatest challenge: my pride and joy, the damage calculator.

Metalkid's calculator is among the most user-friendly out there, but there is still more that it can do. I eventually got tired of memorizing the power of Grass Knot on particular Pokemon. Other moves in that calculator are also not functional.

Therefore, I wanted to make a calculator that had the user-friendliness of Metalkid's, but was also functionally better than Metalkid's. To have each and every single move worth accounting for fully functional; that was my dream. But I'm too old! I can't do it anymore! (Just kidding.)

I encountered many setbacks in the production of the damage calculator. I even had to redo the entire damage formula because of a few mistakes! (Believe me, the damage formula used to be over 8000 characters long! Even Excel 2007 couldn't handle the entire formula! I had to put it all in two cells!) Now, the damage calculator works great, and I have included with it the actual damage formula so that users can learn exactly how damage is calculated. It also makes it easier for me to make changes to the formula, if need be, and for you to check my work!

Well, I certainly drew that one out too long. Time for what you came here for.

I have included in this thread screenshots of the file itself so that you know what you're getting yourself into.

--Screenshots (from Excel 2007)
Base Stats Table (the main part of the resource)
Stat Calculator
Damage Calculator (part 1)
Damage Calculator (part 2 - damage display)
Damage Calculator (part 3 - damage formula)
Lookup Tables (part 1)
Lookup Tables (part 2 - move table)
Lookup Tables (part 3 - item table)
Lookup Tables (part 4 - type effectiveness chart)

Now for the file.

Pokemon Resource 1.0 (Excel 2007 version) - 236.58 KB (original version)
Link 2 (no wait; file comes Zipped--used to, anyway)

Pokemon Resource 1.0 (Excel 2003 version) - 588.5 KB
Link 2 (no wait; file comes Zipped--used to, anyway)
(Someone PLEASE check the functionality of this version! Excel 2003 is greatly limited in relation to 2007, thus I had to change around some things to make the damage formula work. I've had little time to test it.)

Anyway, a lot of stuff is explained in the instructions that come with the resource. If you have any other questions, please post here or PM me. (Contact information for the lurkers coming later.)

Enjoy!

File history:
(Coming later as needed)

NOTICE! (3/15 1:15 AM CDT) I just noticed that I forgot to factor in the ability Forecast into the Damage Calculator! It's not a problem, though, because if you need to use a Forecast Pokemon in your calculations, use the cell for Color Change to put in its type. It should still work in the same manner. Remember, the type of a Forecast Pokemon is:

Fire in the Sun.
Water in the Rain.
and
Ice in the Hail.
(No, its type does not change in Sandstorm.)

Otherwise, its type is the same as it usually is.

NOTICE! (3/16 5:32 PM CDT) Sandstorm is increasing the Defense of Rock types for PHYSICAL attacks! This is a simple fix, though. Just replace the contents of cell N85 (N87 in the 2003 version) in the Damage Calculator worksheet with the following (fix is Bolded):

=IF(AND(L28="Sandstorm",VLOOKUP(C26,Table2,3,FALSE)="Special",OR(VLOOKUP(M2,'Lookup Tables'!A3:D500,3,FALSE)="Rock",VLOOKUP(M2,'Lookup Tables'!A3:D500,4,FALSE)="Rock",AND(ISTEXT(M10),M10="Rock"))),1.5,1)

Table2 in the 2003 version is the cell range 'Lookup Tables'!\$I\$3:\$N\$280.

This will be fixed in the next update.

NOTICE! (3/28 3:51 PM CDT) There is an error in the formula for the move Facade. It's doubling in power when the OPPONENT has the required statuses. >< I'll have it fixed for the next update, which should be whenver I can get enough free time.
2. ### LayellAlas poor Yorick!Sprite Leader

Joined:
Nov 5, 2006
Messages:
3,698
Well I've looked through it and I must say this is quite impressive, much better damage calculator then mine for sure.
3. ### Xenon

Joined:
Feb 16, 2008
Messages:
167
The screen shots look great !

Joined:
Jun 27, 2007
Messages:
543
5. ### Xenon

Joined:
Feb 16, 2008
Messages:
167
Wow ! This is really an awesome resource ^^
Thanks a lot ^^
6. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Ah, those links are much better than mine! I just typed in "file upload" on my computer and it directed me to megafileupload. I didn't bother to look for any more. >< I'll edit those links into the OP.

As for the OpenOffice issue, I'm just now downloading it so naturally I hadn't tested its functionality. I'll work on it as soon as I can.

EDIT: Okay, in the original version, I had restricted the content of the Pokemon name cells to the range containing the names in the Pokemon Resource worksheet. For some reason, when OpenOffice opens the file (And I'm having to use the .xls file for this since OpenOffice believes that the .xlsx file is corrupt. ...), it had changed that to the range containing the Base Defense of the Pokemon in the Pokemon Resource worksheet. There was a similar problem with the Move cell.

Here's the solution for that. Simply redefine the ranges that the cell will allow. While selecting the cell, go to the menu bar and select "Data" then "Validity...". Then choose to allow a "Cell range" and put as the source the range of cells that should be in the cell. It might help to try to define the particular range first so at the very least you can copy the range in the correct format to paste into the Source blank.

Note: you might need to save the file as an OpenOffice Spreadsheet, first, as when I opened the file in OpenOffice the first time, it was opened as a "Read-only", and I couldn't make changes to it.

Joined:
Mar 20, 2007
Messages:
1,661

8. ### darkiejust remember no caps when you spell the mans namePublic Relations

Joined:
Dec 25, 2005
Messages:
6,294
Excellent ;)

this is a pretty nifty resource to have. i especially like how it shows the damage for each random number 85-100, that makes it A LOT easier to see the chances of OHKOing or surviving.
9. ### shdw_strkr

Joined:
Jul 26, 2007
Messages:
46
@ CardsOfTheHearts:

(nice piece of work BTW),

and tried to edit in the modified sandstorm calc, including the table name, but when i pushed enter it said that it was wrong and suggested a change, except the change it suggested was the same as what i had typed in.

I hit okay and went to see if it had changed anything, but it still displayed 1.5 for the sandstorm modifier.

Do you have any idea what i could be doing wrong?
10. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Ah, I see what happened because it ended up happening to me, as well. Just copy/paste the following into the Sandstorm Modifier cell:

=IF(AND(L28="Sandstorm",VLOOKUP(C26,'Lookup Tables'!\$I\$3:\$N\$280,3,FALSE)="Special",OR(VLOOKUP(M2,'Lookup Tables'!A3:D500,3,FALSE)="Rock",VLOOKUP(M2,'Lookup Tables'!A3:D500,4,FALSE)="Rock",AND(ISTEXT(M10),M10="Rock"))),1.5,1)
EDIT: Ah, see the bolded part? There was a space created when I pasted this in. Make sure that space isn't there when you put the formula in the cell.

That should fix the problem. If it somehow doesn't, then just pretend that when you're attacking Tyranitar with a Physical attack that there's no Sandstorm.

It turns out that there was a space in the posted fix that threw everything off; good catch. I'll fix that now.
11. ### shdw_strkr

Joined:
Jul 26, 2007
Messages:
46
Yep, all fixed.
12. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
This is the damage calculator I use because it's the only one I trust (other than my own, and mine is currently under re-production).

My main idea for an improvement would be to somehow have a way to easily flip the attacker and defender. I have no idea how you'd do that with Excel, though.
13. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
I've been thinking about putting something like that in, myself.

I'm sure I could program a macro to do that if I really took the effort to do it. I can't imagine the time it'd take to get it right, though. I'd need more time than I can afford, at the moment. (Damn school...)
14. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
Flash Fire appears to have no affect for the attacker.
15. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Which version are you using and which program are you opening it in?
16. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
Excel 2003

Forms of Deoxys other than the mediocre form (the one without a letter) do not work. All Wormadam forms do work.
17. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Okay, I know the Deoxys problem. The Deoxys forms in the tables relevant to the damage formula are being referenced as:

Deoxys - A
Deoxys - D
Deoxys - S

In the Pokemon cells in the damage calculator, there are no spaces in those names.

To fix this, go to the first table in the Lookup Values sheet and the range AG10:AH507 in the Pokemon Resource sheet and remove the spaces from the names. That should remove all of the problems.

Now, as for the Flash Fire issue, do you actually have Flash Fire activated on the Attacking Pokemon? (i.e. Does cell H33 say "Yes?")
18. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
Yes... How else would I attempt to use Flash Fire? =p
19. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
I keep looking at the formula, I keep testing it; I'm finding no problems with it whatsoever. @_@

Here's what the Flash Fire formula is supposed to be in the Damage Formula:

=IF(AND(C8="Flash Fire",H33="Yes",VLOOKUP(C26,'Lookup Tables'!\$I\$3:\$N\$280,2,FALSE)="Fire"),1.5,1)

Now, what calculation were you trying to perform that produced your problem?

Joined:
Jan 27, 2008
Messages:
3,381
21. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970

As for the second one, try Opening it from your browser instead of Saving. Save it after you've opened it.

I'll be replacing the dead link with a corrected version in a little bit. EDIT: Fixed!
22. ### McLovinn

Joined:
Oct 14, 2007
Messages:
2,674
Extremely useful COTH, thanks so much. Now I can breed if my Internet goes down.
23. ### MKDS_PoKE

Joined:
Jan 22, 2006
Messages:
598
Really nice, I really need it. Thanks Man!
24. ### TangerineWhere the Lights Are

Joined:
May 4, 2007
Messages:
3,156
Awesome :]

It'd be really cool if you could put in IV targets for breeding in this, it'd make it even more awesome!
25. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Utilities useful particularly in breeding certainly should be a focus, that much I can say. However, IV breeding targets would be a little ways off as I have some troubles with making a good IV calculator, as it stands now. I'd want it to be as good as MetalKid's.

Anyway, here were several things that I at least have had planned for this, later on:

--IV Calculator

--Hidden Power Calculator (This drove me crazy last time I tried to create it.)

--Tier Calculator (Calculate a Defense Tier of a Pokemon or an Attack Tier to determine the maximum Defense Tier Pokemon that can be OHKOed--along with a display of Pokemon that would be OHKOed.)

--offline version of X-Act's Defense EVs applet

--(possible addition to damage calculator) Attack EV Calculator to determine how many more EVs would be needed to KO a Pokemon

--KO Calculator that determines the minimum damage needed to OHKO/2HKO/etc. a Pokemon assuming entry hazards, status, etc. -- and/or a way to determine the probability of KO

--IV Breeding Probabilities Calculator

Given the time, I could do a lot more with this than just those. Anyone else with ideas?