troubleshooting Question

Index Match Array?

Avatar of Jeffrey Smith
Jeffrey SmithFlag for United States of America asked on
Microsoft ExcelSpreadsheetsMicrosoft Office
5 Comments2 Solutions182 ViewsLast Modified:
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
    ASKER CERTIFIED SOLUTION
    Join our community to see this answer!
    Unlock 2 Answers and 5 Comments.
    Start Free Trial
    Learn from the best

    Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

    Andrew Hancock - VMware vExpert
    See if this solution works for you by signing up for a 7 day free trial.
    Unlock 2 Answers and 5 Comments.
    Try for 7 days

    ”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

    -Mike Kapnisakis, Warner Bros