Redistribute percentages in a range of cells across a smaller range

[LordClick]

I am trying to solve the following problem.Example.xlsx

I have various percentages spread across a range of cells on a row, covering e.g. 12 columns. The cells represent the quarters of a year, so there are 12 quarters. Not all cells have to contain a percentage.

What I am trying to do on a new row is to compress the time to e.g. 7 quarters (or any other number of quarters) and redistribute the percentages proportionally across the 7 quarters. This means that it isn't a simple operation of combining two cells into one, which I could do if I were to compress the range from 12 to 6 cells, but rather it should shift and redistribute part of a percentage into one cell and part into another cell so that it creates a smooth and accurate compressed redistribution across time, e.g. the 7 quarters.

It's hard to explain, but I attached a small example with the original row on top with 12 quarters, and the new row below with 7 quarters, but I can't figure out what a formula would have to say in order to compress the percentages in the 12 quarter range into a 7 quarter range.

Any help would be great!

[shg]

12 quarters in each year -- those are loooong years.

Please Login or Register  to view this content.

In C3 and copied down, =IF(B3="", "", B3 + N(C1))

The formula in E4 and copied across and down is

=IFERROR(PERCENTILE(yRng, PERCENTRANK(xRng, $A4 * (ROWS(yRng) - 1)/ E$1, 6)) - SUM(E$2:E3), "")

xRng RefersTo: =INDEX($A:$A, ROW(Sheet1!$A$2) + 1):INDEX(Sheet1!$A:$A, MATCH(conBig, Sheet1!$C:$C))

yRng RefersTo: =INDEX($C:$C, ROW(Sheet1!$C$2) + 1):INDEX(Sheet1!$C:$C, MATCH(conBig, Sheet1!$C:$C))

conBig RefersTo: =1E+307*17.9769313486231