Index Match Array?

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!

    LVL 2
    Jeffrey SmithOwnerAsked:
    Who is Participating?
    I wear a lot of hats...

    "The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

    byundtMechanical EngineerCommented:
    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.
    =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


    Experts Exchange Solution brought to you by

    Your issues matter to us.

    Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

    Start your 7-day free trial
    Jeffrey SmithOwnerAuthor 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 ;)

    byundtMechanical EngineerCommented:
    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?

    Jeffrey SmithOwnerAuthor 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.
    Jeffrey SmithOwnerAuthor Commented:
    Yet another timely, clear & complete solution from one of my early &  favorite EE Experts!  Thanks, Brad!
    It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
    Microsoft Excel

    From novice to tech pro — start learning today.