We help IT Professionals succeed at work.

Index Match Array?

178 Views
Last Modified: 2017-03-24
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

    Mechanical Engineer
    CERTIFIED EXPERT
    Most Valuable Expert 2013
    Top Expert 2013
    Commented:
    This problem has been solved!
    (Unlock this solution with a 7-day Free Trial)
    UNLOCK SOLUTION

    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
    CERTIFIED EXPERT
    Most Valuable Expert 2013
    Top Expert 2013
    Commented:
    This problem has been solved!
    (Unlock this solution with a 7-day Free Trial)
    UNLOCK SOLUTION

    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!

    Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

    Get Access
    Why Experts Exchange?

    Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

    Jim Murphy
    Programmer at Smart IT Solutions

    When asked, what has been your best career decision?

    Deciding to stick with EE.

    Mohamed Asif
    Technical Department Head

    Being involved with EE helped me to grow personally and professionally.

    Carl Webster
    CTP, Sr Infrastructure Consultant
    Empower Your Career
    Did You Know?

    We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

    Ask ANY Question

    Connect with Certified Experts to gain insight and support on specific technology challenges including:

    • Troubleshooting
    • Research
    • Professional Opinions