1. New to the forums? Check out our Mentorship Program!
    Our mentors will answer your questions and help you become a part of the community!
  2. Welcome to Smogon Forums! Please take a minute to read the rules.

Programming Pokemon Resource (for DP; made in Excel)

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

  1. CardsOfTheHeart

    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.

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

    Pokemon Resource 1.0 (Excel 2003 version) - 588.5 KB
    Link 1 (c/o 2shared; download link will appear at the bottom of the page within seconds)
    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. Layell

    Layell Alas poor Yorick, I knew him, Peter Gabriel too!
    is an Artistis a Forum Moderatoris a Pokemon Researcheris a Smogon Media Contributoris a Community Contributor Alumnus
    Sprite Leader

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

    Xenon

    Joined:
    Feb 16, 2008
    Messages:
    167
    The screen shots look great !
    But I'm not able to download the file :(
    Please help :)
  4. timw06

    timw06

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

    Xenon

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

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

    Miltonic

    Joined:
    Mar 20, 2007
    Messages:
    1,661
    i cant download from either places. :(

    Edt: ok, i was finally available to download it.
  8. darkie

    darkie mfw i see alison brie
    is a member of the Site Staffis a Smogon IRC AOPis a Super Moderatoris a Smogon Media Contributoris a Contributor to Smogonis a CAP Contributor Alumnusis a Battle Server Moderator Alumnus
    Public Relations

    Joined:
    Dec 25, 2005
    Messages:
    6,093
    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

    shdw_strkr

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



    I just downloaded your Pokemon Resource ('03),

    (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

    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

    shdw_strkr

    Joined:
    Jul 26, 2007
    Messages:
    46
    Yep, all fixed.
  12. david stone

    david stone Fast-moving, smart, sexy and alarming.
    is a Site Staff Alumnusis a Smogon IRC AOp Alumnusis a Programmer Alumnusis a Super Moderator Alumnusis a Researcher Alumnusis a Contributor Alumnusis a Battle Server Moderator Alumnus

    Joined:
    Aug 3, 2005
    Messages:
    5,150
    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

    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 stone

    david stone Fast-moving, smart, sexy and alarming.
    is a Site Staff Alumnusis a Smogon IRC AOp Alumnusis a Programmer Alumnusis a Super Moderator Alumnusis a Researcher Alumnusis a Contributor Alumnusis a Battle Server Moderator Alumnus

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

    CardsOfTheHeart

    Joined:
    Aug 21, 2007
    Messages:
    1,970
    Which version are you using and which program are you opening it in?
  16. david stone

    david stone Fast-moving, smart, sexy and alarming.
    is a Site Staff Alumnusis a Smogon IRC AOp Alumnusis a Programmer Alumnusis a Super Moderator Alumnusis a Researcher Alumnusis a Contributor Alumnusis a Battle Server Moderator Alumnus

    Joined:
    Aug 3, 2005
    Messages:
    5,150
    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

    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 stone

    david stone Fast-moving, smart, sexy and alarming.
    is a Site Staff Alumnusis a Smogon IRC AOp Alumnusis a Programmer Alumnusis a Super Moderator Alumnusis a Researcher Alumnusis a Contributor Alumnusis a Battle Server Moderator Alumnus

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

    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?
  20. RBG

    RBG It feels like a perfect night to dress up like hipsters
    is a Super Moderatoris a Smogon Media Contributoris a Site Staff Alumnusis a Smogon IRC AOp Alumnusis a Researcher Alumnusis a Tiering Contributor Alumnusis a Battle Server Moderator Alumnus
    Facebook Manager

    Joined:
    Jan 27, 2008
    Messages:
    3,343
    I've tried downloading the 2003 one from both locations with 2 separate browsers. The 30 second wait one doesn't exist anymore, and the other stops downloading at 423 kb.
  21. CardsOfTheHeart

    CardsOfTheHeart

    Joined:
    Aug 21, 2007
    Messages:
    1,970
    OK, I see. The first link wasn't downloaded frequently enough.

    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

    McLovinn

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

    MKDS_PoKE

    Joined:
    Jan 22, 2006
    Messages:
    598
    Really nice, I really need it. Thanks Man!
  24. Tangerine

    Tangerine Where the Lights Are
    is a Smogon IRC SOPis a Team Rater Alumnusis a Forum Moderator Alumnusis a Smogon Media Contributor Alumnusis a Tiering Contributor Alumnusis a Contributor Alumnus

    Joined:
    May 4, 2007
    Messages:
    3,155
    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

    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?

Users Viewing Thread (Users: 0, Guests: 0)