1. 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. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
Another simple suggestion that would be a huge addition would be to let people select stat vs. EV individually for defender and attacker. Lots of times I just want to check how much Special Attack I need to OHKO Garchomp with Ice Beam, for example, and to do that I have to find a Pokemon in the right range.
2. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
The Stat Calculator could help you do such a thing, but a more convenient way to do it would be nice, yes.

*puts that on his to-do list
3. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
The Sandstorm 'fix' breaks the 2003 calculator. I pasted

=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)

into cell N87 and did nothing else. It gives a #NAME error.
4. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
The only difference between that and the actual fix is the space between the 1 and 0 in the last reference to M10. Still, that shouldn't cause a #NAME error...

Try replacing Table2 in the formula with:

'Lookup Tables'!\$I\$3:\$N\$280

and see where that gets you.

This is what I get for putting this together in Excel 2007 and converting it for use in 2003. The conversion process hasn't exactly been smooth.

...

Wait! You wouldn't happen to have downloaded the Office Compatibility Pack, have you? That allows Office XP and 2003 to recognize the 2007 file formats so that you won't have to use that bugged 2003 version of the file. (Yes, I consider it bugged because there were several features from the 2007 file that had to be discarded for the 2003 version. I might have made a mistake making the necessary adjustments to the 2003 file.)

I wish I actually HAD Excel 2003 so I could look into it further. Excel 2007 doesn't seem to be able to reproduce your results.

Joined:
Aug 3, 2005
Messages:
5,152
6. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
Didn't work. "The converter failed to open the file." :(
7. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Damn... and I can't remember if there's anything else necessary to make that work. ><

Shoot, do you still have that problem? I'm not sure what you could do to remedy it...

Anyway, I'll be getting out of school in a week and a half (Bachelor's degree finally done! :D) so I could have a newer version up within the next month, if not sooner. The damage calculator is getting a couple of signficant upgrades that will make life quite a bit easier. I'll tell you more when it comes out. ;)
8. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
UPDATE!

(I finally got around to doing this...)

Here's an update to the resource! I decided to do this after making a couple of significant changes to the damage calculator (besides making some aesthetic adjustments) such as:

Done! Now there is a "Switch Pokemon" button at the top of the calculator that allows you to switch the Attacking Pokemon data with the Defending Pokemon data.

Done! Now each side has a "Use EV/IVs or Stats?" option so that you can do just that.

Okay, here are the files. Enjoy!

Pokemon Resource 1.1 (2007 version)
Pokemon Resource 1.1 (2003 version)

P.S. One of the big hurdles I had with trying to create an IV Calculator has recently been solved. Who knows? If I get around to it, the next update might have one...
9. ### eric the espeonmaybe I just misunderstood

Joined:
Aug 7, 2007
Messages:
3,694
This looks very good, the one thing I would like would be if you could add the Pokemon made by the Create a Pokemon project, here is a link to their info.
You don't need to add the Mountineer ability.
10. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
I'll consider it. It won't be a priority due to the other things that need to be done first, but I suppose enough people would be interested in it. (And yes, I would eventually need Mountaineer. ;) )
11. ### eric the espeonmaybe I just misunderstood

Joined:
Aug 7, 2007
Messages:
3,694
Thank you, I have had a very hard time trying to find a damage calc that works with them.
Mountineer is not really important as it is a simple negation of all rock damage (so easy to do in your head), but would require a new checkbox "switching in". But if you think its worth it, it could be useful.
12. ### cimhappiness is such hard work

Joined:
Jun 3, 2007
Messages:
5,413
This is super badass. You should put in something to check if EVs are being wasted or not (like a +nature on a low stat and EVs in a high stat is wasting evs). Pretend that made sense.
13. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
To be honest, I have no idea how I would start going about to doing that. I would need a better way to define that in Excel, the land of if-statements and lookups. XD
14. ### StallionThis is it...

Joined:
Jun 11, 2006
Messages:
6,076
I had a set of damage calculators almost the same as yours, but they got deleted :(, so I will have to do with yours. Thanks for posting them.
15. ### MetalKid

Joined:
Aug 5, 2005
Messages:
246
What about the Hidden Power calculator are you having trouble with? Did you want it to produce results like the 2nd part of mine does? If so, I could show you the VB.NET code that it took to create. It might help you out. Though, I do agree... It was very difficult to code in a coding language. I can't imagine what it would take with Excel formulas...
16. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
Yes, trying to create something that does what the second part of your calculator does has been a real bitch.

You see, there are two problems I'm facing. One, I've been trying to make the calculator return sets of IVs for each stat. I place each set of values into a cell and attempt to make the formulas return that set of values in a display similar to yours, but I don't know how to do that without splitting the sets into individual cells and using a ton of lookups--I'm not looking forward to that. I can't find a way to simplify the process.

The other problem is one of just sheer volume. For example, a Hidden Power of Bug-type and 50 BP has ten different sets of possible IVs according to your calculator, MetalKid. Quite frankly, I just don't know how to account for them all.

I'm sure these problems could be solved through persistence >_<, but having that code wouldn't hurt the process of trying to figure it out, that's for sure.
17. ### WichuKittens

Joined:
May 30, 2007
Messages:
1,547
I'm working on a similar resource for a school project. I've got an IV calculator and a Hidden Power calculator already - the HP calculator isn't complicated if you know what you're doing. I've uploaded mine - you're free to use it if you still need it (Excel 2003 format). Download HP Calc only.
18. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
That Hidden Power calculator looks nifty, but I actually have a simpler form of it in the damage calculator. It's actually the calculator that does the REVERSE of that process that I'm dreading putting together. But thanks anyway.

I wouldn't mind seeing that IV calculator, though. It might actually save me some trial and error. Who knows?

P.S. I've had other things distract me, as of late, so I haven't been working on this. I MIGHT work on it soon, however. I'm just not looking forward to the time-consuming chores of putting together the IV and Hidden Power calculators, but at least they're reasonably doable for once. I couldn't have said that a month ago. xD
19. ### WichuKittens

Joined:
May 30, 2007
Messages:
1,547
The IV calculator isn't very good... It uses the stat formula to calculate the stats for each IV between 0 and 31, then uses the VLOOKUP function to find the minimum and maximum values. However, it does work. I'll upload it as soon as I get access to my school files.

Oh, and sorry about the HP calculator misunderstanding. I see what you're trying to do. I might attempt that later.
20. ### david stoneFast-moving, smart, sexy and alarming.

Joined:
Aug 3, 2005
Messages:
5,152
Unfortunately, this does not seem to open with Open Office.

That being said...

There needs to be a checkbox for whether the target has already moved to properly account for Payback. It also needs a checkbox for whether the target has taken damage to account for Assurance.

Power Trick needs to be added.

Joined:
Jan 27, 2008
Messages:
3,407
22. ### CardsOfTheHeart

Joined:
Aug 21, 2007
Messages:
1,970
I haven't had any access for a while so this has been neglected. I'm not sure when I'll be able to use my laptop again...

That said, I'll try and address the posts here while I still have time on this public library computer:

Obi:
I thought I would have an OpenOffice version of this available by now, but I haven't gotten around to it. I'll make that a priority.

As for Payback and Assurance, I thought I had already accounted for Payback, but I haven't looked at the file in so long that I don't remember. I'll make sure to put them in.

RB Golbat:
I was aware of the item boost before sandstorm a while ago, but I got lazy in fixing it. Nice find on Me First, though. Fortunately, with the way I've set up the Damage Formula in the worksheet, both fixes are really simple.

I don't know how long it will be before I can work on this again. It shouldn't be too much longer before I can get my internet access back (roughly a week--at least it better be), but I'm starting my Master's degree program in a week from Monday, and I can't reasonably expect to have any free time between now and December--nor between January and May, for that matter.

After all that, though, I should have my Master's degree and be a little more flexible with my time. Until then, don't expect any updates...
23. ### RBGTrying to get my Smods back D:

Joined:
Jan 27, 2008
Messages:
3,407
ACtually, its Tinted Lens that is in Mod 3, i made a typo. oops....
24. ### StarBP

Joined:
Jul 11, 2008
Messages:
41
So, you are saying that Metalkid's IV calc is no good? His calculator uses much the same principle as yours does, but in HTML, not Excel.
25. ### RBGTrying to get my Smods back D:

Joined:
Jan 27, 2008
Messages:
3,407
FYI, for gyro ball, here is the speed formula:
Final Speed Stat = Current Stat x Stat Modifier x Speed Ability Modifier x Speed Item Modifier x Paralysis Modifier x Tailwind Modifier

just the same as obi predicted