troubleshooting Question

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
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.