# Index Match Array? Jeffrey Smith used Ask the Experts™
on
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
Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Jeff,
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.
=SUMPRODUCT(E4:E32*(\$D4:\$D32=1))/Rate
=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

Staff-Demand-ForecastQ29011291.xlsx

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 ;)

Jeff
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Jeff,
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? 