Calculations by details -formula/VBA help needed

Sorry if my title is kind of cryptic. I need formulas, which I'll then put into a macro I'm writing. But I need to figure the formulas first.

Here's the overview of the need and the problem:
We have contracts in place. Some contracts cross regions, some cross PACs (program areas), some cross LBB Accts (specific expenditure accounts). In creating projections (which is done by region, and within region by PAC and LBB Account) we base our projections on past expenses. We want to limit our projections by any limits on our contracts. So, we first figure the expense by region in dollars and % of the total contract expense. We multiply the expense % by the contract amount to determine the max contract amount for the region (IE If Region has spent 10% of the expense, their "Limit" is 10% of the max contract amount). We then narrow it down to the PAC level, and will need to narrow it down to the LBB Acct level.

I can't change that methodology, but I need. to figure what kinds of formulas I could put in place to determine these amounts, knowing that every month new contracts are going to come in, and expenses against new LBB Accts and new PACs. Any help is greatly appreciated, and if I more explanation is needed please let me know.

Thanks,
John

The attachment shows information I have (in green), and information I need (in red). The formulas I have in place work for the old data I have in place, but each month I'll be adding more (see the bottom part of the "Spread of Max" sheet). When I add new lines in, some of the formulas will have to be recreated/changed to incorporate the new details.

Not sure if this will help... but....

First I created a Named Formula to determine the Last Row dynamically.

Named Formula: LRow refers to:

Please Login or Register  to view this content.

Then in G2 formula is:

Please Login or Register  to view this content.

copied down

in H2 formula is:

Please Login or Register  to view this content.

copied down

in J2 formula is:

Please Login or Register  to view this content.

The rest of the formulas are untouched.

This formula:

Please Login or Register  to view this content.

finds the last row containing text in column A.

REPT("z",255) creates a string of 255 z's. MATCH() looks for this string in Column A... and since we did not use the Match_type 3rd argument, then Match() looks for the last cells that contains text that is smaller than or equal to the string of z's.. which would obviously be the last cell with any other text.

The other formulas are not easy to explain.....but I will try....

Note: If you use the Formula Audit tool in Tools|Formula Auditing|Evaluate Formula you can follow the steps of the Formula evaluation.

The formula in G2:

Please Login or Register  to view this content.

first checks to see if the current value in C is the same as the previous row's value, and enters a blank if it is. This is so the same result isn't repeated down the "group of Regions". If it is not the same, it assumes it is the first of the group and calculates the sum of the group.... To identify the group (since you have duplicates even further down that you don't want to include, we can't use Sumproduct), we have to figure out where the group ends....

this part:

Please Login or Register  to view this content.

does that. It starts at current row's F cell and goes to where the first time a Region in C doesn't match the current row's region. MATCH(TRUE,INDEX(....))) creates an array of TRUEs and FALSEs based on if each item in Column C matches current C value or not. It returns the position of the first TRUE and Indexes that agains column F to return the bottom of the SUM range. The -1 is to back up one row so that it goes only to the last row where C matches current row C value.

The formula in H2 is similar but it is grouping by column B (or CNTRCT_ID)... but this time we need to always refer to first and last row in specific CNTRCT_ID group in each cell within column H of the respective groups in order to acquire the SUM range...

So then the F2 after the SUM( opening is replaced by:

Please Login or Register  to view this content.

This finds the last time that a value in column B is not equal to the current column B value... so MATCH is now looking for a 2 in the results of 1/(B$1:B2<>B2). The results will either be 1 because 1/TRUE is 1 or #DIV/0! error because 1/FALSE is 1/0 which is error.... so again there is no 3rd Match_type argument in this MATCH() function and so it looks for the last time a value in the array is less than or equal to 2... the function ignores errors and so finds the last 1 and returns the corresponding indexed cell reference from column F.... to indicate the first of the group...

Hope this helps explain it a bit...

Don't fret too much, some of this stuff really takes time and dedication to learn.. it is a conglomeration of stuff I have learned over the years using Excel and on these forums... It becomes fun when you can take the tidbits and put them together to make something useful....

Hi Jomili,

I'd start by creating a Dynamic Named Range of the ever increasing rows of data.
See http://support.microsoft.com/kb/830287 or
http://chandoo.org/wp/2009/10/15/dyn...t-data-series/

Then to do a totals for your region or the other two groups, I'd play around with Pivot Tables. I'd be summing the amounts and either using the region etc for rows or columns and possibly filtering. The secret to this method is you can add the amounts to the summation area a second time and "Show As" a percent or a lot of other values. see http://faculty.fuqua.duke.edu/~peckl...ot%20Table.pdf

This is how I'd tackle this problem if I understood it a little better I may even be able to come up with a solution. I don't understand the formulas involved yet.