PDA

View Full Version : Enhancement planning using a spreadsheet



EllisDee37
08-15-2013, 06:53 AM
When I plan out enhancements for a build I typically move stuff around all the time. Ron's planner is an excellent utility and I do use it to validate my builds and post them to the forums, but for actually planning out enhancements I use a spreadsheet for the bulk of the work.

Let's look at my Pale Trapper enhancement list for the U19 enhancement pass for an example. I haven't verified that this loadout is fully compliant with AP gating yet; this is the rough draft. (I verify by simple visual inspection.)

The first thing I do is manually enter the name, tree, tier, AP cost and level for every enhancement in every available tree I might want. Note that the level I enter is an adjusted value that may be higher than normal due to splashing levels in another class, requiring a feat I don't take until higher level, etc... In this example, level 1 wizard enhancements aren't available until level 2 since I take a rogue level @ 1. You can also see the levels where I take the metamagic feats pretty clearly, since I "improve" them immediately.

After adding every enhancement I might want from all trees available I fill column G with TRUE for all of them. The running total at the top of column H shows me how many I've selected so I can see at a glance how much I need to prune.* Note that I keep the top row frozen so I can always see the running total. The formula for column H (included in the screenshot) is pretty straightforward, and the header for colum H is a simple =SUM(H2:H200). 200 is just an arbitrarily high value to ensure it catches them all.

Once all enhancements are added and flagged as TRUE I sort the list by Tree => Tier => Enhancement. Then I go through and starting deleting TRUEs to prune the list down to 80.

Once I'm down to an 80 I'm happy with, then it's time to verify if this is a valid list of 80 AP. For this I sort the sheet by Train => Tree => Tier and manually count the AP spent in each tier to make sure I meet "points in tree" prereqs. Once I have a valid set of 80 points worth of enhancements, it's time to order them.

Ordering them is my favorite part; I love intricate planning. First I reorder the sheet to Train => Order => Level, which is the final sort order. Each time I add a level or 2 worth of ordering I quickly re-sort. (For order notation, 3.2 means "second enhancement taken at level 3.") I typically choose a few key enhancements as "set in stone", taken as early as allowed. For this build the markers were the core enhancements, empower/maximize/quicken efficient metamagics, and I wanted 1 Int taken at levels 7, 8, 11, 12 and 20 to complement a previous-life +4 tome combined with levelups.

After the ordering is complete (and while setting the order) I use column I to verify that my order satisfies "points in tree" requirements. This used to be a much simpler affair for the old enhancement system with its "total AP spent" progression. For "points in tree" I had to write a macro to call, sending cell ranges to the macro. More info on column I's formula and implementation in the second post.


http://img163.imageshack.us/img163/3039/y7ia.png (http://imageshack.us/photo/my-images/163/y7ia.png/)

Uploaded with ImageShack.us (http://imageshack.us)


*With only 86 AP chosen for wizard it was not hard at all to prune the list down to 80. For my other builds it's usually between 100 - 130 AP total.

EllisDee37
08-15-2013, 06:54 AM
You can see the formula for column I in the screenshot below.

=PointsSpent(D$2:D2,H$2:H2,D2)

PointsSpent is a macro I wrote for this, though it may not be necessary. I don't know worksheet functions very well, so I often go straight for code. Complete code is down below. The macro takes three paramaters:

D$2:D2
The first parameter is a range object. This range contains the "Tree" column from the first row to the current row. The $ means absolute reference, not relative, so it always starts from row 2. When you propagate this formula to fill the whole column, it becomes D$2:D3, D$2:D4, D$2:D5, etc.. Always row 2 to the current row.

H$2:H2
Similar to the previous parameter, this range object contains the actual AP spent. (AP spent is toggled on/off using the TRUE column.)

D2
This is simply the current row's "Tree." This parameter is how the code knows which AP from the ranges to add up; if the "Tree" column is equal to this parameter for a row, that row's AP is added. Otherwise it's skipped.

So now looking at the image below, consider the enhancements at the start of level 5. Row 12 shows we've spent 6 AP in the Pale Master tree. This total is inclusive, meaning we'd already spent 5 AP in tree before taking this 1 AP enhancement. This satisfies the "points in tree" prereq for a tier 2 core ability, so we're good to go. Row 13 shows we've spent a whopping 12 AP in Archmage, so that should be safe for a while. Time to go heavy on PM!

You can see from the screenshot in the OP that my order does not satisfy all points in tree prereqs; School Mastery taken at level 10.2 fails, as it's a tier 4 but I haven't yet spent 20 AP in the AM tree. As I said in the OP, this order was a rough draft.

http://img706.imageshack.us/img706/8461/ey2c.png (http://imageshack.us/photo/my-images/706/ey2c.png/)

Uploaded with ImageShack.us (http://imageshack.us)

Here's the complete code:
Private Function PointsSpent(prngTree As Range, prngAP As Range, pstrTree) As Long
Dim lngAP As Long
Dim i As Long

For i = 1 To prngTree.Rows.Count
If prngTree(i, 1) = pstrTree Then lngAP = lngAP + prngAP(i, 1)
Next
PointsSpent = lngAP
End Function

I'm using a very old XP box with Excel 2002, so I can't say for sure how to work with macros in newer versions of excel. If you have no idea how to add that code, the easiest way is typically:

Record a Macro (type in random letters) and Save Macro.
Edit Macro.

When you choose Edit Macro the code window will (hopefully) open up. You can then copy the above 9 lines of code to the bottom of that window and then save the spreadsheet. With any luck, that's all you need to do.

Arnhelm
08-20-2013, 09:49 AM
Thank you for this. I'll give it a shot. Much nicer than using pnp as I'm doing now until something better is available.

Ok, back to trying to recreate a useful Barb mix... :(

slydyr22
08-20-2013, 02:58 PM
Thank you for this. I'll give it a shot. Much nicer than using pnp as I'm doing now until something better is available.

Ok, back to trying to recreate a useful Barb mix... :(



Too bad it's useless now with the new update!

EllisDee37
08-20-2013, 03:02 PM
Too bad it's useless now with the new update!Not sure I follow. It's specifically for the new update.

CaptainPurge
08-20-2013, 10:25 PM
You can see the formula for column I in the screenshot below.

=PointsSpent(D$2:D2,H$2:H2,D2)

PointsSpent is a macro I wrote for this, though it may not be necessary. I don't know worksheet functions very well, so I often go straight for code. Complete code is down below. The macro takes three paramaters:

D$2:D2
The first parameter is a range object. This range contains the "Tree" column from the first row to the current row. The $ means absolute reference, not relative, so it always starts from row 2. When you propagate this formula to fill the whole column, it becomes D$2:D3, D$2:D4, D$2:D5, etc.. Always row 2 to the current row.

H$2:H2
Similar to the previous parameter, this range object contains the actual AP spent. (AP spent is toggled on/off using the TRUE column.)

D2
This is simply the current row's "Tree." This parameter is how the code knows which AP from the ranges to add up; if the "Tree" column is equal to this parameter for a row, that row's AP is added. Otherwise it's skipped.

So now looking at the image below, consider the enhancements at the start of level 5. Row 12 shows we've spent 6 AP in the Pale Master tree. This total is inclusive, meaning we'd already spent 5 AP in tree before taking this 1 AP enhancement. This satisfies the "points in tree" prereq for a tier 2 core ability, so we're good to go. Row 13 shows we've spent a whopping 12 AP in Archmage, so that should be safe for a while. Time to go heavy on PM!

You can see from the screenshot in the OP that my order does not satisfy all points in tree prereqs; School Mastery taken at level 10.2 fails, as it's a tier 4 but I haven't yet spent 20 AP in the AM tree. As I said in the OP, this order was a rough draft.

http://img706.imageshack.us/img706/8461/ey2c.png (http://imageshack.us/photo/my-images/706/ey2c.png/)

Uploaded with ImageShack.us (http://imageshack.us)

Here's the complete code:
Private Function PointsSpent(prngTree As Range, prngAP As Range, pstrTree) As Long
Dim lngAP As Long
Dim i As Long

For i = 1 To prngTree.Rows.Count
If prngTree(i, 1) = pstrTree Then lngAP = lngAP + prngAP(i, 1)
Next
PointsSpent = lngAP
End Function

I'm using a very old XP box with Excel 2002, so I can't say for sure how to work with macros in newer versions of excel. If you have no idea how to add that code, the easiest way is typically:

Record a Macro (type in random letters) and Save Macro.
Edit Macro.

When you choose Edit Macro the code window will (hopefully) open up. You can then copy the above 9 lines of code to the bottom of that window and then save the spreadsheet. With any luck, that's all you need to do.

http://funny-pictures-blog.com/wp-content/uploads/2011/09/Nerd.jpg