Jeffrey Smith

asked on

# Index Match Array?

Hi Experts,

I have a somewhat complicated issue that I

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":

To complicate this further, I want to sum the Hours,

So, in the attached example file, I want to come up with formulas that will populate the range

I hope this is clear, but if not, please let me know. I appreciate anyone's help!

Jeff

Staff-Demand-Forecast.xlsx

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":

**(< $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**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 (***ONLY the 1st payment***>= 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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

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.

ASKER

Yet another timely, clear & complete solution from one of my early & favorite EE Experts! Thanks, Brad!

ASKER

Good to hear from you again ;)

Jeff