Programming Excel EV Optimizer

[VoidedNote]

Edit: Hey everyone, Happy New Year's Eve! I posted this on Christmas, but I've revamped it and made it much much better! New features:

1. Idiotproofing (you can't mess up things you shouldn't be touching)
2. Speed optimizer (described below)
3. Totally redone interface (the last one was pretty bad)
4. MASSIVE reduction of manual entry (another big turn off)

So, what does it do? Well, it optimizes EVs to survive certain attacks. You put in your Pokemon, as well as two attacking Pokemon (one physical, one special) and their respective attacking moves. The algorithm will find you a spread that enables you to survive both attacks with the minimum number of EVs possible. The table to the right will also show every possible spread that will work. You can also change it to allow you to survive two hits of the given attack (or 1.5, for moves like Draco Meteor that drop the attacking stat by 2), and even say "I'm okay with an X% chance to be KO'd, I'm an Amoongus taking a Heat Wave from Charizard Y".

It now also allows you to optimize your speed to outspeed certain threats. Ever run something with an awkward speed stat that doesn't see enough play to warrant 252 investment? This is the tool for you. This tool also factors into the bulk optimizer, preventing spreads from going over 510 EVs.

Important Notes:
1. If you only care about surviving an attack on one side or another, you can put the BP of the attack on the other side to 1 (this can also be used for the speed optimizer too). Only way you're not tanking a 1 BP attack from anything is if you're a Shedinja and I question your judgment if you're EVing Shedinja with this.
2. I appreciate feedback on anything that isn't clear, or any errors, but double check everything because it is easy to make a mistake like having the wrong attack stat or the wrong attack type, or Huge Power Talonflame [bird spam intensifies].
3. The x-ilate abilities do not change the type of the move. If you are using Pixilate, make the move fairy type instead of normal.
4. Berries currently do not work at the moment, I'm going to implement this sooner or later, along with Parental Bond (just aim to survive 1.5 attacks instead, same thing).

Link is here: https://www.dropbox.com/s/c99s386eunex2yl/EV optimizer.xlsm?dl=0

 

[VoidedNote]

Bump. Massive revisions!

 

[P3DS]

http://survivalcalc.appspot.com/

That is what I currently use to optimize my bulk spreads for VGC.

Currently yours lacks defensive abilities like filter and friend guard, and the ability to turn off abilities. Also, I may just want to hit a specific target with a given number of EVs, as I could have put in the EVs for offensive output. For example, I'm using a specs zapdos at the moment, and I wanted it to survive adamant kang's Double edge, with the rest going into sp. def (the EVs for speed and offense was sorted out before). From survival calc I got a spread of 252HP / 116Def / 52SpD, whereas I get a spread of 60HP / 236Def / 124 SpD. Physically, they should be the same (Bar the fact that the spread from yours doesn't guarentee the survival of kang's double edge, so it goes to 68/236/116), but look at the special side

252+ SpA Choice Specs Pixilate Sylveon Hyper Voice vs. 252 HP / 72 SpD Zapdos: 111-132 (56.3 - 67%) -- guaranteed 2HKO after Leftovers recovery
252+ SpA Choice Specs Pixilate Sylveon Hyper Voice vs. 68 HP / 116 SpD Zapdos: 105-124 (60.3 - 71.2%) -- guaranteed 2HKO after Leftovers recovery

As you can see, it is better for the first spread.
Also, special HP numbers are good to have. Like reduced damage from burn, 5 Subs, 101 Subs even (For OU players more). Also, the ability to maximize/minimize HP. With my zapdos again, a higher HP stat is the best for me. It lets me get the chance to KO with thunderbolt on kang, after the double edge, since kang took more damage than at the lower HP, while a pokemon like scrafty, who likes drain punch, prefers a lower HP, as it regains a larger percentage of it's HP each turn, or sub seed whimsicott, as it can regain a larger % of HP, and then sub up.

Also, maybe an instruction page might be good, and the layout tidied up a little, as it was a little difficult to get my head around the different parts.

Overall, a great tool, since survival calc isn't usually around at hand. It still has a long way to go to tear me away from survival calc, but it can get there.

 

[VoidedNote]

It looks like the link is still linking to the old one, despite being updated. I think I fixed it. All your complaints were addressed in the new version and the damage formula was changed to accurately reflect damage calc. I also added items and abilities. The berries do not work at the moment, but I'll be updating that some time soon. The only other further revisions I plan to add are sand defense boosts and adding Parental Bond (currently you can just set number of hits to survive as 1.5 to get the same effect). As for your complaints about HP, there's a table of 64 different possible spreads at most, so you can figure out which ones suit your needs best. Keep in mind the numbers at the top will opt for HP over defenses.

As for your damage calc example, you should notice that your first calc uses 72 spD, not the 52 spD you said you got. The algorithm will not work with your approach; getting good mixed bulk cannot be achieved by finding the most efficient bulk for one side and then just dumping EVs into the other stat. Physically, my spread is superior because it allows for much greater freedom of EVs, but this is because it's truly optimal. The spread you got uses 368 EVs as opposed to just 304 from mine. For special attacks, however, it is lacking. Finding the optimal physical bulk often means sacrificing some special bulk and vice versa. Mixed bulk sacrifices a little on both ends, finding neither the true optimal spread for physical or the true optimal spread for special.