Furmula to reduce 3 columns to 2

[j_Southern]

Workbook Test26 shows the left hand table of 3 groups of three columns headed GEN TAP and LAB next to it is a table of three groups of two columns

This table is derived from the first by splitting the value in the GEN column and adding a fraction (governed by the value in row5) to both the other columns.
AS the real data has over 100 sets of three columns I would like to do this by devising a single formula that can be dragged across the 2 column table. I feel it should be possible but I cant see how. Can any one help please
John

[Pete_UK]

When you say in your attached file that you have 100+ groups, do you mean that you have 100+ rows, or 100+ (*3) columns of data? If the latter, then the formula will not be in cell O8.

Pete

[Bo_Ry]

Please try at D8

=INDEX($D$5:$L$5,(COLUMNS($O8:O8)+1)+(COLUMNS($O8:O8)-1)/2)*INDEX($D8:$L8,INT((COLUMNS($O8:O8)-1)/2)*3+1)+INDEX($D8:$L8,(COLUMNS($O8:O8)+1)+(COLUMNS($O8:O8)-1)/2)

[j_Southern]

Pete,
I mean 100+(*3) Cols of data to be reduced to 100+(*2) Columns.

I can't see a problem with O8. The manual formula is there and can be dragged down through the rows. I am hoping for a formula that could also be dragged across the 200+ columns in the second table.
John

[queuesef]

I'm attaching a copy of the formulas below the range, rather than to the right side as you have it. This will allow you to COPY the formulas below to a much larger range. Once you have formulas BELOW the data, you can copy the formulas in a BLOCKs of 3. It's hard to explain but your copying a Block in multiples of the block you're copying. [ if you were copying 5 columns of data, then you would copy the formula to columns to the right in multiples of 5 ( 5, 10, 15, 20 etc) Good luck

[Pete_UK]

If you have 3*(100+) columns, it means the cell references will need to be changed, and it might be better to have the second table on a different sheet. To keep the rows the same, I put the headings TAP, LAB, TAP, LAB etc. on row7 of Sheet2, starting in column A, and then used this formula in A8:

=INDEX(Sheet1!$D:$L,ROW($A8),INT((COLUMNS($A:A)-1)/2)+COLUMNS($A:B))+INDEX(Sheet1!$D:$L,ROW($A8),INT((COLUMNS($A:A)-1)/2)+COLUMNS($A:B)-(MOD(COLUMNS($A:A)-1,2)+1))*INDEX(Sheet1!$D:$L,5,INT((COLUMNS($A:A)-1)/2)+COLUMNS($A:B))

You can copy this across, but it will only pick up those 3 groups of data - you will need to adjust the $L reference to suit your real data (shown in red).

This is the formula I developed to go in cell O8 of Sheet1, as requested:

=INDEX($D:$L,ROW($A8),INT((COLUMNS($D:D)-1)/2)+COLUMNS($D:E))+INDEX($D:$L,ROW($A8),INT((COLUMNS($D:D)-1)/2)+COLUMNS($D:E)-(MOD(COLUMNS($D:D)-1,2)+1))*INDEX($D:$L,5,INT((COLUMNS($D:D)-1)/2)+COLUMNS($D:E))

Copy across and down as required.

Hope this helps.