Using LotroPlan for Essence Gear
[Let me know if you find any errors or have any suggestions.]
Note: this post contains a number of screen shots. To see them full size, click on the picture.
LotroPlan is a spreadsheet file that makes it possible to keep track of and compare stats for different configurations of gear. In my experience, it has been quite accurate matching the in-game stats. It contains the details for almost everything level-cap characters can use.
I used it to plan my 105 essence gear for Michaelo, Nodread and Wrytune, and when I was done upgrading, the in-game stats were exactly what the spreadsheet predicted. Because of the expense in time and resources to do a set of essence gear, it is important to decide what you want to achieve and avoid mistakes.
You can read the history and discussions about LotroPlan in the forums www.lotro.com/forums/showthread.php?539545-LotroCalc-calculate-stats-amp-plan-upgrades. Note that the subject line says LotroCalc, the original name, instead of LotroPlan.
LotroPlan was created and has been updated by Giseldah. The over 50,000 items in the database were provided by Castorix. It's a lot of work figuring out exactly how stats work in the game and collecting the information for all those game items, as well as building the spreadsheet to make evaluation and comparisons easy. They have made a significant contribution to the Lotro community.
LotroPlan requires OpenOffice or LibreOffice, which are free open-source office suites. It does not work with Microsoft Office Excel. It is a complex spreadsheet that uses macros to perform the detailed calculations needed for accurate stats, and those macros are why it doesn’t work with Excel.
I use OpenOffice www.openoffice.org. Like LibreOffice and Microsoft Office, there are multiple applications in the office suite. LotroPlan uses only the Calc spreadsheet program. I was able to download and install it with no problems.
It is possible some details of how it works will be different with LibreOffice.
There are some quirks about OpenOffice Calc. I haven’t used LibreOffice, so I don’t know if it behaves the same way, but it might because they share the same code base.
- Opening the file is slow. Wait until you see the “Welcome to LotroPlan” dialog before trying to use it.
- Saving the file is also slow.
- It periodically saves a copy for recovery. When it does, the spreadsheet becomes unresponsive until it finishes.
LotroPlan is a single spreadsheet file you can download it from Google DropBox using the link at the end of the first post in this thread on the LOTRO forums: www.lotro.com/forums/showthread.php?539545-LotroCalc-calculate-stats-amp-plan-upgrades. As of this writing, the latest version is 3.8 and the link is www.dropbox.com/s/ioplw9cl7jubv5d/LotroPlan%203.8.ods?dl=0 but it may change.
You do not need to have a DropBox account. If prompted to log in, you can click “No thanks, continue to download”. Because the spreadsheet contains macros, it is possible your anti-virus software might alert. I always scan the file, and have not had any issues with it, but that is a risk if the file gets compromised on DropBox.
Copy the file to a location where you will be working on it. I save copies from time to time in case I make a big mistake and want to go back. I recommend saving a copy of the original downloaded file in case you accidentally damage the file and have to start over.
When you open the spreadsheet for the first time, it will look like this. You can click on the picture to see the full size image.
At the bottom, there are tabs for different sheets in the file. The first one, labelled “Character Name”, is an example character. You will copy this sheet to make your character, changing it to match what you currently have in game. Then you will make a copy of that sheet, and modify it to reflect the changes you want to make. The sheet shows the differences between the two configurations.
What you see at the top of the sheet are the stats for your character.
Scrolling down the sheet, you will see an area where you will enter your gear.
Note the column headings with different stats. The other sheets in the spreadsheet labelled Jewelry, Medium (Armour), Essences, Food, etc. contain descriptions of different gear. You will copy rows from those sheets and paste them into the gear area of your character’s sheet to set the gear you are or will be using. Here’s an example:
The data and formulas in a row extend far to the right. When copying a row, you must select the whole row by clicking on the row number at the left. It is safe to copy multiple rows at once by click-dragging through the row numbers, or by shift-clicking or control-clicking. Be sure you are getting the whole row, not just some of the columns.
Scrolling down past the totals for your gear you will see an area for Trait stats, buffs, consumables, and other sources of stats beside your gear. You will copy lines from the other sheets to this area as you did with your gear.
Scrolling down again, you will see an area where you can specify your virtues, stat tomes, and racial traits.
There are blank lines below it that are not used by LotroPlan. You can put whatever you like there. When I am playing with different options, I will copy things like all the t8 supreme essences there so I can copy the ones I want up to the gear area without having to switch sheets. I will also move the temporary buffs like food and scrolls there from the buff area when I want to see my unbuffed stats.
Once you have a sheet that reflects the current state of your character, you will make a copy of your sheet. Back at the top of the sheet, under “Compare with”, you will select your old sheet. As you make anticipated changes to the new sheet, the right column will show you the stats from your old sheet for comparison.
Setting things up for the first time for a character will take some time. If possible, you will want to be able to switch back and forth between the game and the spreadsheet. You might try running the game in a window, even if you don’t normally play that way, just to make it easier to switch between the game and LotroPlan.
Make a copy of the Character Name sheet
I recommend not changing the “Character Name” sheet that LotroPlan starts with. The sheet contains macros and is carefully constructed. If a sheet you are working on gets damaged, you may be able to recopy the original sheet, rather than having to start over with the original file.
To copy a sheet, right-click on the tab at the bottom and select “Move/Copy Sheet”. Note that you must check Copy, or it will just move the sheet. You select the position where you want the new sheet to be. I put my character sheets to the left and leave all the sheets with the data to the right. Rename the new sheet by right-clicking on the tab, so you can keep track of which sheet represents what.
Set the race and class using the drop-down menus and enter the level. Now it is time to discard the gear in the example character.
Left-click to select the row just below where it says Item stats (row 50). Scroll down to just above the labels for the totals and shift-left-click that row (129). Hit Delete. It will bring up a dialog box to select what you want to delete. I recommend “Delete all”, which will completely clear the rows of formatting, since your gear probably won’t match the example.
In this area you will copy the rows for the different pieces of gear you have, and they will be added up. You can delete unneeded blank rows or insert more rows if and where you want them. You can arrange or rearrange the rows however you like.
To finish clearing out the example data, scroll down and left-click the row below Trait tree passives (137), shift-left-click the row above the labels for the totals (160), then hit Delete.
This is a good time to save the file, and perhaps save a copy in case you want to start over. The remaining parts of this section can be done in any order.
Armour and Jewellery
The major source of stats is the armour and jewellery you wear, and different pieces will provide different stats. Fortunately, LotroPlan has data for most of the items for level-cap characters.
The easiest way to find a piece is do a search. Control-f brings up the Find dialog. Be sure to click on “More Options” and check “Search in all sheets” so it will search through the data sheets. Enter all or part of the name of the item in the “Search for” field. Note that it is a simple substring match. If it doesn’t find the item you want, click Find again, since there are many items with similar names.
If you don’t find the item by searching, be sure you have the correct name. You can also browse the sheets with the data. The ItemsDB sheet has an alphabetical list of over 50,000 items!
When you find the item you want, click on the row number to select the entire row. Type control-c to copy the row, click on the tab for the sheet where you want to put it, click on the row number where you want it to go, then type control-v to place the row.
Repeat this process for other items.
When you copy an item to the gear area, check that the stats match the item in the game. If the stats don’t match, there may be different items with the same name, so search for an item with the correct stats first.
Some items, notably Epic Battle jewellery, have stats that are scaled to the item level. If you click on the stat cell, you will see it has a formula like “=CALCSLICE(…)” instead of a constant. If so, in your gear area, change the item level cell (iLvl column B) to the item level of the actual item. It should adjust the stats to the correct values.
Essences in items are handled as separate rows. When I first entered my character, I put the essences immediately below the piece they were in. After I had completely entered my character and verified that the stats matched the game, I rearranged the rows in the gear area so that all my essences were together.
Moving rows in the gear area is easy. Select the row by clicking on the row number you want to move, type control-x, click on the row number where you want it to go, the type control-v. You can rearrange the rows in the gear area however you like, the order doesn’t matter. For example, after I put my essences all together, I selected the rows and sorted them (Under the Data menu select Sort) so I could see how many of each essence I had.
Legendary Items contribute to stats in multiple ways. First, find the template line for the Legendary Item, either imbued or not (Forged). Some LIs have so-called passive stats that are generated randomly when the LI is created, but scale according to the age of the LI and the tier of the main legacy. Note there are different colours for the names indicating whether it is First, Second or Third Age. After copying the row for the LI, you can edit the type and name in the column A cell. If necessary, edit the number of star-lit crystals (for non-imbued LIs) or the tier of the main legacy (for imbued LIs) to adjust the passive stats. Simply delete the cells for passive stats you don’t have. If your LI does not have passive stats, you can use the generic “Weapon: Name” rows whether it’s imbued or not.
Stat legacies are also scaled according to the legacy tier and the number of star-lit crystals (for non-imbued LIs). Copy the stat legacy row, then edit the number(s) in the column A cell to have it calculate the proper stat value. If you have difficulty getting it to calculate the right stats, you can simply enter numbers in place of the formulas, since it will not affect anything else.
LotroPlan is not able to handle all legacy effects for all classes. If some are important, you can add a row and put in the stats that it contributes.
The Legendary Title and four relics also contribute stats, so copy the rows for them as well.
Different classes may have other objects that contribute to stats. Hunters have a tome and an off-hand weapon; guardians have a shield and a bow; etc. Remember to find and copy the rows for those items.
Traits, Buffs, and Consumables
Some of the rows in the Traits, Buffs and Consumables section are static; others are temporary, depending on the use of skills or consumable items.
First, find any static effects, such as traits that confer specific stats. For example, Guardian’s Defence increases armour by 10%. Minstrel’s Enduring Morale adds an amount of morale depending on how many points are put in the trait. Static effects will have to be included for the stats shown in the spreadsheet to match the stats in the game.
It is also helpful to know what the stats will be in likely situations. Suppose you will use warding scrolls that increase your mitigations. There is no need to exceed the mitigation cap, so you want to arrange your gear to reach the cap with the scrolls.
Some buffs are based on skills, but may be active when it is important. For example, Guardian’s Ward raises the block and parry rating (by an amount affected by legacies if present), and it is common for guardians to keep it active at all times during combat. It is useful to see those buffs when evaluating gear.
Copy the rows for consumables such as scrolls and food that you will use to this section.
I save a copy the rows in this section at the bottom of the sheet, so that I can delete them when I want to see the unbuffed stats, for example, to confirm that they match the game. I copy them back when I want to evaluate my stats when they are in use.
Virtues, Tomes, and Racial Traits
LotroPlan makes it easy to enter and compare changes in virtues and traits. Select the 5 virtues you have slotted using the drop-down menus. Be sure you have entered the ranks of at least the virtues you have slotted. If you want to consider slotting other virtues, enter all the ranks and it will show you the stats you could get.
Select the ranks for any stat tomes you have used. Indicate whether or not you have slotted any racial traits.
Check your work
If your stats match the game values, congratulations! If not, you may want to track down where the differences are.
If the difference is only a small amount of morale, there is a morale buff for being in safe areas like Bree or the Shire. Travel to Galtrev or someplace less hospitable and see if your morale changes to the right number.
If the difference is very small, it is possible there is a rounding error. Some stats that are displayed as whole numbers in-game are in fact rounded from numbers with digits after the decimal point. Algorithmic variations can explain the difference.
If a particular stat is off by some amount, scroll through the rows looking at that column to see if you find an incorrect row. Virtues and trait trees can affect a variety of stats, so verify that you have the proper ones slotted and that the ranks are correct.
Another way to look for errors is to make a copy of your character sheet. Remove individual pieces of gear in-game and delete their corresponding rows in the copy of the sheet to see where the discrepancy is. Try removing your LIs first, since they can have some complicated effects that may not be handled in LotroPlan. If you have taken off all your gear in the game and deleted the entire gear section in LotroPlan and the stats still don’t match, look more carefully at trait trees, slotted traits, slotted virtues, and stat tomes.
It is, of course, possible that LotroPlan has a bug that is giving the wrong results, but I have found it to be very accurate.
LotroPlan makes it easy to compare different configurations of gear, traits, etc. The basic idea is to copy a sheet and make changes to it to see what the results will be. You select which sheet you want to compare to, so you can easily see the effect of different changes. You can create as many variations as you like.
Copying your character
The first thing to do is to copy the sheet you have created. Right-click on the tab for the sheet and select “Move/Copy Sheet”. Be sure to check “Copy”, then select where you want it to go. I usually keep the sheets I am working on to the left. Right-click on the tab and select “Rename” to give it a name so you know which sheet is which.
At the top of the sheet, select the sheet you want to compare with. You can change this at any time, so if you have several variations, you can compare them to each other.
Column B shows the stats for the current sheet, and column C shows the stats for the sheet you are comparing to. Column D shows the net change.
Columns I and J show percentages for the stats. If you set the zone to “PvE” it will calculate for ordinary content and tier 1 instances. If you set the zone to “PvE T2”, it will apply the tier 2 reductions so you can see what the effective percentages will be in tier 2 instances.
Column E allows you to set targets for the percentages. If you set targets, Columns F and G show how much above or below the target you are. To clarify, Column D shows the net change from the other sheet to this sheet. Column F shows how much above or below the target this sheet is, column G shows how much above or below the target the other sheet is.
Additional targets and comparisons are to the right.
Converting Ratings to Percentages
If you click the “Show Caps” button, it will bring up a list of the stats with maximum ratings and percentages. Click on the name of a stat on that list and it will display a graph of that stat, so you can see how the percentage relates to the rating.
Click on the “Show Caps” button again to dismiss the display.
To work on my essence build, I first deleted the old armour, jewellery and essences from the gear area. I copied the rows for the new armour and jewellery pieces to the gear area. I needed more room, so I inserted some blank rows.
Because I was trying to decide how many of which essences I wanted to use, I counted the number of t7 and t8 essence slots I had, and reserved that many rows for the essences. I copied the list of essences from the “Essences” sheet to the area at the bottom of the sheet so I could copy the ones I wanted from there without switching sheets.
Evaluating other items with essences
Essences have radically increased your ability to get the stats you want. They change how you can think about other items. Suppose you want more Tactical Mitigation. You might get it from a relic, or a virtue, or an essence, or another piece of gear. How do you decide the best way to get it?
Since you need multiple stats, the question is what combination of items, essences, virtues, etc. will give you the best overall stats. One relic gives you Physical Mastery, another gives you Critical Rating. You want both, and whichever you don’t get from the relic, you can get from an essence.
One way to decide is to calculate what fraction of an essence is represented in each stat on an item. For example, a True Rune of the Dark Wood has about 1/3 of a Morale essence and less than 1/10 of a Physical Mitigation essence. A True Rune of the White Mountains has about 1/4 of an Agility essence and 1/2 of a Physical Mastery essence. Since these are all stats I need, I get the most benefit from the latter. What I miss by choosing it, I can make up with essences.
This analysis is why these days most people focus on getting Resistance from virtues. The other stats they provide can be obtained in larger quantities from essences. Together, the 5 virtues that provide the most resistance give you 7 essences worth of resistance. Assuming you want at least that much, you’re better off getting your resistance there, and using essences for other stats.
Plans B, C, etc.
Once you have a plausible candidate, it’s time to copy the sheet again. Now you can compare a different combination of essences and other items to your first set. In the same way, you can compare any number of changes. For example, with Nodread, I compared using Featured Instance armour, which has two slots per piece and can use tier 8 essences to using Minas Tirith barter armour, which has 3 slots per piece but can use only tier 7 essences. The Featured Instance armour provides a lot of Physical Mitigation, but a guardian reaches the cap with Armour rating alone, so that is a wasted stat. I got better overall stats with the Minas Tirith armour.
Making the New Build
Once you have a plan you want to make, you may want to rearrange the rows to indicate which essences will go in which piece. One reason is guide the construction process so you don’t forget an essence. Another consideration is the opportunity to swap pieces of gear to achieve different goals.
Suppose I have a ring with two morale essences. I might make a ring with two agility essences for situations where I want more DPS and morale isn’t an issue. Because you need to use Essence Reclamation Scrolls to shuffle essences, you may want to plan ahead.
With luck, when you finish your build, the game stats will match the LotroPlan sheet. You’ll be ready for your next upgrade.
by Michaleo on 2016-09-24 03:04:32