Results 1 to 6 of 6
  1. #1
    Community Member
    Join Date
    Feb 2011
    Location
    Argonnessen
    Posts
    8,710

    Default Enhancement planning using a spreadsheet

    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.




    Uploaded with 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.

  2. #2
    Community Member
    Join Date
    Feb 2011
    Location
    Argonnessen
    Posts
    8,710

    Default

    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.



    Uploaded with ImageShack.us

    Here's the complete code:
    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.

  3. #3
    Community Member Arnhelm's Avatar
    Join Date
    Sep 2009
    Location
    Denver, CO, USA
    Posts
    2,261

    Default

    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...
    There is no free lunch.
    Ghaelii MacLeigh, TR3 Rogue, Officer of Tyrs Paladium, Ghallanda -- Arndis, TR3 Monk -- Hargold, 28 Barb
    No drama -- http://tyrspaladium.forumotion.com/

  4. #4
    Community Member
    Join Date
    Aug 2013
    Posts
    1

    Default Too Bad

    Quote Originally Posted by Arnhelm View Post
    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!

  5. #5
    Community Member
    Join Date
    Feb 2011
    Location
    Argonnessen
    Posts
    8,710

    Default

    Quote Originally Posted by slydyr22 View Post
    Too bad it's useless now with the new update!
    Not sure I follow. It's specifically for the new update.

  6. #6
    Community Member CaptainPurge's Avatar
    Join Date
    Oct 2009
    Location
    In a van down by the river
    Posts
    751

    Default

    Quote Originally Posted by EllisDee37 View Post
    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.



    Uploaded with ImageShack.us

    Here's the complete code:
    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.
    Cannith: Ortarr (leader of Killer Dwarfs) - Purge Bravely - Drunkungfu Master - Ortarrdo Son of Ortarr - *others*

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

This form's session has expired. You need to reload the page.

Reload