Useful Excel Documens I Made for URPG

View attachment URPG EM Chart.xlsx | View attachment URPG EM Chart.xlsx More recent version where it says "MOST RECENT VERSION".

I made this spreadsheet for tracking EM prices and values. It's laid out in this format:

TM# | TM Name | TM Price | Quantity (enter a value here) | Total price of that many

At the bottom there's a row that has total price of all TMs (doesn't include BM/MT/SM) ($621,500), followed by the number of EMs you input, and then the total price of those EMs. The row above that allows for BM/MT/SMs to be added. The first one on the left is sorted by TM#, the second one on the right is sorted alphabetically. I made these entirely myself based on a suggestion by Pidge. Enjoy! The TM prices were manually entered, so if you spot an error please let me know. I had Excel and the Pokémart open side by side. These are .xlsx files.

MOST RECENT VERSION (Updated here April 29, 2015 with price updates): View attachment 96854

(Updated here April 29, 2014 with the minor TM price changes from Pokémart modifications in February 2014, if you already filled out one for yourself either look up and change those TMs or download this one and copy column C into your filled out one): View attachment URPG EM Chart Blank v2.xlsx

FOR FFA CALCULATOR DESCRIPTION, SEE POST 5. File is there, but I'll also put it here for convenience. Updated July 25, 2015 with a list of Pokémon stats in second tab. View attachment 98531

 

Re: Spreadsheet to calculate EM Values/Prices

Wow did you make this just now? Nice work!

 

Re: Spreadsheet to calculate EM Values/Prices

Alphabetical and everything! Very snazzy

 

Re: Spreadsheet to calculate EM Values/Prices

Making that was easy. Excel can do a lot if you know how to use it. The hard part was that after that I decided to expand it and add every single Pokémon I own into it. I can post that if people want to see what that looks like and I can make a blank version of that one (aka make a copy and remove all my Pokémon from the copy) if people want that.

Also, thanks both of you

EDIT: Here's mine: View attachment URPG EM Chart.xlsx

And here's the blank one: View attachment URPG EM Chart Blank.xlsx

........................................ | Pokémon | Pokémon | Pokémon | Pokémon...................
TM# | TM Name | TM Price | 1 if have | 1 if have | 1 if have | 1 if have................... | Total price of that TM | Total number of that TM
...
...
...
| BM/MT/SM | 5000 | # BM/MT/SM | #BM/MT/SM | #BM/MT/SM | #BM/MT/SM..... | Total price of BM/MT/SMs | Total number of BM/MT/SMs
Total# | | | #EMs on Poké1 | #EMs on Poké2 | #EMs on Poké3.................. Total price of everything | Total number of everything
Total$ | | | Price of Poké1 | Price of Poké2 | Price of Poké3............................................................................

Sorry if that explanation is confusing. IM me with questions.

It SHOULDN'T explode if you expand it by highlighting the whole right two columns (CX and CY) and copying them over further, then deleting the original two columns. There's about 100 spaces right now.

 

Okay, so I made something for FFAs in Excel too, which I've heard at least one ref ( @Buzzer) express interest in. Here it is: View attachment FFA Calculator.xlsx

Explanation of how it works, though it's mostly self explanatory:

Pokémon | % HP | Notes (visible) | Modified Speed | Current HP | Max HP | Unmodified Speed | Speed Multiplier | Speed Divider | Notes (invisible) | Stats (copy this column)

Pokémon: For the first column, enter whatever name you want to be displayed for the Pokémon.

% HP: Don't touch this column, it's automatic.

Notes (visible): Anything that you want to be displayed in stats besides name and HP. This would probably include things like Sub, Ref2, SpAtt+2, etc.

Modified Speed: Don't touch this column, it's automatic. However, it is useful because you can highlight this column (excluding row 1) and have it sort it by speed. Currently the speed is not set to display in stats, but making it do so would be very simple.

Current HP: Put the actual amount of HP the Pokémon has here. This is NOT a percent. Say the Pokémon has 354 max HP, uses Sub (88 HP), then takes a Hyper Voice that does 103 damage, then Roosts (177 HP). You can input (without quotes) "=354-88-103+177", adding the relevant term as it happens if doing so is easier, BUT TO DO SO YOU MUST START WITH AN =.

Max HP: Put the Max HP of whatever Pokémon it is before initial stats. Unless your special rule involves evolving or something, you shouldn't have to change it again. It is initialized as 1 because the dividing by 0 error on % HP was annoying.

Unmodified Speed: The Pokémon's natural speed in URPG. Again, shouldn't need to be changed after initializing other than evolution, mega evolution, or Transform.

Speed Multiplier: If the Pokémon has speed rises, Tailwind, or some other similar effect, put it here.

Speed Divider: If the Pokémon has speed drops, paralysis, or some other similar effect, put it here. This column was added because it was annoying with how it was trying to deal with repeating decimals created by using 2/3 or something. I don't remember exactly, it was a while ago.

Chart for both Speed rises and drops, obviously in opposite columns (paralysis should be x4 to whatever is in divider and Tailwind/Swift Swim/Chlorophyll/Sand Rush/Unburden should be x2 to multiplier, Quick Feet x1.5, all compounded multiplicatively. If you put "=2*2*3 for something with Tailwind, weather speeding, and Speed+4, it should do it for you but I will cry for your inability to do that yourself):
Speed±1: 1.5
Speed±2: 2
Speed±3: 2.5
Speed±4: 3
Speed±5: 3.5
Speed±6: 4

Notes (invisible): Anything you want to leave as a reminder for yourself but to have no effect on stats. Useful for abilities or anything else hidden.

Stats (copy this column): When you have entered everything else and are ready to post stats, highlight as far down as your FFA goes, then simply do a copy/paste into the FFA chat. If you have additional effects like Trick Room or weather, you can replace the row below the last Pokémon with them.


Should the unthinkable happen and an FFA exceeds the 30 people it is currently set up for, simply highlight the entire last row and drag the bottom right corner down as far as needed (copying all cells down as far as you drag, formulas will automatically update based on their new position). When the FFA is done, either do that with a blank row or simply close without saving.

Some tricks I've come up with, but that maybe don't work for you: When something is KO'd, make the speed negative. Do it in such a way that when it is sorted, they will end up in the order they were KO'd. Remember that if a cell's text gets too big, you can double click the right side of the header row's box for that column to automatically resize based on largest entry, or click and drag to custom resize it.


If anyone needs the .xls version for old Excel instead of the .xlsx version I put here for new Excel, let me know. I can't guarantee all features will work properly. Additionally, I'm looking to add more features at some point. I think if I had the current damage formula as used in URPG, I MIGHT be able to get it to do calculations. No promises there. And obviously if I did do that, I'd need to run a bunch of random tests to ensure that it does indeed produce the same result. It is possibly I could use the Pokémon database from Ball's calculator get it to automatically have base health, speed, and if calculations were added the other stats, but I may need to learn a bit more about how to get Excel to behave well for that. Anyways, go nuts FFA refs who feel like using it, let me know all feedback so I can rejoice in what is done right and fix what can be fixed. If you have any further questions about it, anyone who would be using it should easily be able to find me to ask. If you're just curious about it and haven't seen me around AIM or Skype, reply here or leave me a VM or contact me on AIM at MewAlcadiesMew. You can also find me from URPG's Skype chat.

 

New version of the price spreadsheet! View attachment URPG EM Chart Blank v2.xlsx

Fixes:
Forgot something in the formulas that probably broke a bunch of them if you tried to resize it.
Instructions for fixing your own copy, if you'd rather not redownload it. Seriously though, it's probably easier to just copy/paste the data section of yours into a new one.

Spoiler

In case you want to fix the one you already have, in the last two columns, the formulas should read "=SUM(D#:CW#)" where # is the row number (CW should be the furthest right column that isn't one of these. If it's not CW, use the one it actually is. If it's not D, it should be D and that's the error). Replace it with "=SUM($D#:CW#)". The $ forces it to keep using D as the left bound no matter where you drag that cell to. Now use drag that replacement through from CY2 down through CY186. Again, if you've resized it it may not be CY, but it should be the furthest right column. The one to the left of it should have a slightly longer formula, but again in the SUM section add a $ before the D#. Drag that replacement from CX2 to CX170, CX173 to CX183, and CX187 to CX191. This does NOT add any of the new features of this version, only fix that bug about resizing.

New features:
Added a number index for Pokémon at the bottom.
Created columns for average spent on each item and average number of each item purchased. These are based on the number of Pokémon you have and as such, each TM's average number should be less than one. NOTE: These are currently based on the cell in the Pokémon number row under the last Pokémon slot. To get accurate numbers, either resize it to properly fit how many Pokémon you have or manually replace the 98 that should be there with the correct number.
Added an entry that is the sum of the non-HM EMs and DW abilities. In case you care how many things you actually paid for (or otherwise got from some sort of effort). It appears in CY188 (EM+HM+DW $ row, Item Count Total column).

Notes:
If you're resizing, instead of dragging things over and deleting, it's easier to just insert or delete columns. Both should work fine if done right though.
If you're using this to keep track of your individual Pokémon the way I do, the only boxes you should be manually entering something other than 1 or 0/blank cell is base cost, BM/MT/SM, or on very rare occasions, DW (if you have a Scolipede/Chandelure/Zapdos/etc. with both HAs unlocked, in which case it should be a 2).
I use the AIM account MewAshMew now. Don't try to IM MewAlcadiesMew, I won't get it. Any questions, feel free to ask in this thread, by PM/VM, or on any messenger.

 

Here's a new version with the updated prices for Swift's recent post and formatting for Soft-Boiled corrected. If you already use an old one and want to simply update it, just copy the second column and paste it into the one you use and fix Soft-Boiled yourself or copy that too.

Newest version, April 29, 2015: View attachment URPG EM Chart Blank v2.xlsx

 

Updated FFA Calculator to have a second sheet with all Pokémon's URPG stats: View attachment FFA Calculator 2.xlsx