Dynamic Find and Divide

I am working with several hundred columns of data that is composed of totals and the breakdowns from those totals. The columns are sequential so I have a total, then a handful of columns that are a breakdown of that total (and sum to that total) and then a column that is another total, etc. I have written a simple formula that identifies when a column is a new total and when it is a subset of another total (false represents a new total). So all of all the “true” columns will sum up to the nearest false column to their left, until there is a new “false” column.

Now I would like to calculate percentages for all of the numbers. So I need to be able to find the nearest corresponding total and divide it into the subtotal. For example, here is a mock of the data and what I want:

FALSE TRUE TRUE FALSE FALSE TRUE TRUE TRUE
Total 1 Subtotal 1 Subtotal 2 Total 2 Total 3 Subtotal 1 Subtotal 2 Subtotal 3
USA 10 5 5 12 19 5 5 9
Japan 15 6 9 11 22 11 8 3
Mexico 5 4 1 8 13 3 6 4

USA 100% 50% 50% 100% 100% 26% 26% 47%
Japan 100% 40% 60% 100% 100% 50% 36% 14%
Mexico 100% 80% 20% 100% 100% 23% 46% 31%


The ideal solution would do the calculation in a new tab, so I can preserve the raw data and the percentages. It should also be noted I have hundreds of rows and not three like in this example. Example also attached. Thanks for any and all help![/SIZE][/FONT]