+ Reply to Thread
Results 1 to 4 of 4

Formulas for shifting targets.

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,708

    Formulas for shifting targets.

    This post is related to another in the Programming section, http://www.excelforum.com/excel-prog...h-a-twist.html if you would like more background.

    I'm trying to create an automated method of creating a forecast (I've got twelve users around the state who will be using it) and the staffing piece is only the first step. That first step is a doozy to try to automate. I've provided an example of my desired output. I have everything including the column titles up to my "Adjusted", and that's where I'm stuck. I know the formulas I need to place in my columns if I can see my columns, but the number of columns is variable, depending on how many months my user has chosen for his history to project from, and the number of months remaining in our fiscal year to project for (FY is September through August).

    For instance, in my "Adjusted" columns, the number needs to be the "Base Projected" if that equals or is less than the "Authorized". However, if "Base Projected" is greater then the "Authorized" the value needs to be the last amount amount reported in our History months. I've got the formula I need, but don't know how to adapt it to this situation. The formula (which works in the example attached) is
    Please Login or Register  to view this content.
    My problem is that I, K, and G are all shifting targets, so I don't know how to structure my formulas to catch them.

    To further complicate the matter, after this Staffing projection I'll be doing a Salary projection, which will build on the values in the various columns of the Staffing projection, so I'll need formulas to reference back to these columns. My head is already reeling!

    BTW, I also have a word document I created for my users to do all the steps. They're not the most Excel literate (kind of like me!) so the doc is full of pictures and explanations. If anyone wants to take a look at it and help me automate it all I'd be most overjoyed. Let me know and I'll post it or send it to you.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,846

    Re: Formulas for shifting targets.

    I don't know if you can work with this; however, what I did:

    1. I named column B "MonthlyAverage"
    2. I named column D "ProgramCode"
    3. I named column H "GrandTotal"
    4. I named column K " AuthorizedFTEs
    4. I Named column W "HistoryRange" (TO BE HIDDEN)
    6. In cell U5 I created the formula:
    Please Login or Register  to view this content.
    and copied down for all rows

    The intent is to dynamically modify the address range of the history columns as months are added or deleted between the ProgramCode column and the GrandTotal column.

    Then I,

    7. For the Grand Totals per record, created the formula:
    Please Login or Register  to view this content.
    that, because it depends on the dynamic address range, adjusts as months are added/deleted between ProgramCode and GrandTotal
    maybe this will give you some ideas...
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,708

    Re: Formulas for shifting targets.

    Ben,

    Wow! You did a lot of work here! Thank you.

    Going to the example you posted:

    Columns C through H (Including Grand total) come from my original pivot table, so there's no need for a formula for the Grand total. I'm thinking the history range you provided could be useful, but I'm not sure just how. For instance, right now the sample shows my history is in E:G, which means Grand Total is in H, and my Base Projected starts in I. However, I only selected 3 history months; I could have chosen 10, which would have changed where Base Projected starts. The number of Base Projected months is dependent not on how many history months are used, but on how many months need to be projected (if latest history month is Feb, then March through August need to be projected. If latest history month is August, then September through August need to be projected.)

    The key to the # of columns isn't the History months, but the Base Projected months. Whereever they exist, the next column is my "Allocated", for which I can plug in the formula. However, beginning in "Adjusted", I need to first identify how many columns I need (same as "Base Projected) then populate them with my formula
    Please Login or Register  to view this content.
    "). My macro creates ALL of the column headings, just no formulas. If I use the existing formulas I need to having a macro to identify/change I,K, and G to whatever pertinent columns exist, so I either need flexible formulas or an identifier macro. Your idea of naming the columns may come in handy there; I hadn't thought of that angle.

    I'll be pulled away on other tasks today but will check back whenever I'm able. I appreciate any help or insight you can provide.

    Thanks,
    John

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,708

    Re: Formulas for shifting targets.

    I'm finally back to paying attention to this post, and find that no one else has offered a suggestion. I'd sure appreciate some help on this one.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1