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