Index Match Array?

Jeffrey Smith
Jeffrey Smith used Ask the Experts™
on
Hi Experts,

I have a somewhat complicated issue that I think might be resolved with the use of a combination of an Index Match Array formula but can't see how to do it.  That said, there may be other &/or easier ways to solve (perhaps with using "helper columns") but the solution seems to be above my pay grade. Here is the issue (see attached file for details):

I am trying to populate a Staff Demand Forecast which is driven by a Marketing/Cash Flow forecast of expected deals with the following "rules":

    The deals are ranked by Tier based on Deal Size: a)Tier 1 Deals (< $47,500 determined by the amount shown in Column C) should have 100% of the required Staff hours (calculated as the Deal amount in Column C divided by the Named Range "Rate" of $190 each to arrive at the # of hours - in the example file, I have provided "helper columns" R:AC to calculate the number of hours)) allocated in the same month the Deal is expected to close (the month is determined by ONLY the 1st payment in the Marketing/Cash Flow schedule [the subsequent Payments should be ignored because that only deals with Cash Flow implications, not Staff hours forecasting - again, "helper columns" may be easier to solve this]); b) Tier 2 Deals (>= to $47,500 AND <$95,000) will require 50% of the Staff hours in the Month the Deal is expected to close and 10% in each of the subsequent 5 months; and c) >= to $95,000 will also require 50% of the Staff hours in the Month the Deal is expected to close but 5% in each of the subsequent 11 months.

    To complicate this further, I want to sum the Hours, by Tier, for each month in the 3 rows below the Marketing/Cash Flow schedule.

    So, in the attached example file, I want to come up with formulas that will populate the range E34:P36 and I have manually calculated the expected results in the adjacent range R34:AC36 to test the formulas.  To make the manual formulas more easily read, I have separated the Tier calculations in the Formula bar (by using "ALT+[ENTER]" so you will need to drag the bottom edge of your Formula bar border down a couple of rows to see the whole formula.

    I hope this is clear, but if not, please let me know.  I appreciate anyone's help!

    Jeff
    Staff-Demand-Forecast.xlsx
    Comment
    Watch Question

    Do more with

    Expert Office
    EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
    Mechanical Engineer
    Most Valuable Expert 2013
    Top Expert 2013
    Commented:
    Jeff,
    I suggest something like the following formulas. The ones for Tier 2 and 3 need to be tweaked for subsequent cells because they ultimately need to look back 5 and 11 months, respectively. I show the three original formulas plus the two tweaks in the cells with salmon highlighting in the attached workbook.
    =SUMPRODUCT(E4:E32*($D4:$D32=1))/Rate
    =50%*SUMPRODUCT(E4:E32*($D4:$D32=2))/Rate + 10%*SUMPRODUCT(A4:D32,ISNUMBER(A$3:D$3)*($D4:$D32=2))/Rate
    =50%*SUMPRODUCT(E4:E32*($D4:$D32=3))/Rate + 5%*SUMPRODUCT($A4:D32,ISNUMBER($A$3:D$3)*($D4:$D32=3))/Rate

    Brad
    Staff-Demand-ForecastQ29011291.xlsx

    Author

    Commented:
    This looks promising, Brad ! Thanks for this and for posting on my topic.  I'm going to see if I can absorb the meaning of your tweaks & then come back to post further.

    Good to hear from you again ;)

    Jeff
    byundtMechanical Engineer
    Most Valuable Expert 2013
    Top Expert 2013
    Commented:
    Jeff,
    The reason for the tweaks is that the Tier 2 and 3 formulas are designed to look back in time by 5 or 10 months. But the your data are positioned such that 5 or 11 months to the left may fall off the edge of the worksheet. As you might imagine, Excel takes a dim view of such gallimaufry. So the formula uses an absolute reference to column A until it has been copied to the right far enough for that reference to need changing.

    Question: for Tier 3 you look back 11 months. This means that you eventually end up accounting for 105% of the hours (50% + 11*5%). For Tier 1 and 2, you account for 100% of the hours. Was this difference intentional?

    Brad

    Author

    Commented:
    Leave it to you, Brad to send me to my dictionary. Yes, good catch on my Tier 3 calculation. It occurred to me after I submitted the question that I needed to correct that but I didn't want to post again lest Experts see the "post count" was > 1 & think the question was already being tended by other experts & move on (haha). So, yes, I'll need to adjust that formula so that months 2-12 of the schedule record 50/11*.01 (or the percentage  0.0454545455) of the deal's hours.

    As far as my sample file not containing enough columns for the newer deals, I was just trying to keep my example simple 😉 & figured I could adapt the solution to a multi-year schedule. But now I think you've already taken care of that for me. Thanks again, Brad. I think I can close this out now.

    Author

    Commented:
    Yet another timely, clear & complete solution from one of my early &  favorite EE Experts!  Thanks, Brad!

    Do more with

    Expert Office
    Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

    Start 7-Day Free Trial