Avatar of Jeffrey Smith
Jeffrey Smith
Flag for United States of America asked on

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!

    Jeff
    Staff-Demand-Forecast.xlsx
    Microsoft ExcelSpreadsheetsMicrosoft Office

    Avatar of undefined
    Last Comment
    Jeffrey Smith

    8/22/2022 - Mon
    ASKER CERTIFIED SOLUTION
    byundt

    Log in or sign up to see answer
    Become an EE member today7-DAY FREE TRIAL
    Members can start a 7-Day Free trial then enjoy unlimited access to the platform
    Sign up - Free for 7 days
    or
    Learn why we charge membership fees
    We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
    Not exactly the question you had in mind?
    Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
    ask a question
    Jeffrey Smith

    ASKER
    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
    SOLUTION
    byundt

    Log in or sign up to see answer
    Become an EE member today7-DAY FREE TRIAL
    Members can start a 7-Day Free trial then enjoy unlimited access to the platform
    Sign up - Free for 7 days
    or
    Learn why we charge membership fees
    We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
    Not exactly the question you had in mind?
    Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
    ask a question
    Jeffrey Smith

    ASKER
    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 Smith

    ASKER
    Yet another timely, clear & complete solution from one of my early &  favorite EE Experts!  Thanks, Brad!
    Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
    Walt Forbes