Convert Data in rows to be expressed in a column

[EvansB2]

Hi there,

I would like to speed up a process that I'm carrying out manually. With respect to the table pasted, cell B6 to B10 represents the data in row. However, I have to sum and then divide each cell by 2, as can be seen by the formula in cells B6:B10.

The sample given is a snapshot and I have multiple data spreading across many columns and thought it may be possible to have a formula in B6 that allows me to drag down automatically as I currently do it manually!

Any assistance would be gratefully received.

Regards

Ben

OFFSET_Vertical to Horizontal.xlsx
	A	B	C	D	E	F
1	ANNUAL AMOUNTS	YR1	YR2	YR3	YR4	YR5
2		500	300	400	600	900
3
4
5
6	Year 1	400
7	Year 2	350
8	Year 3	500
9	Year 4	750
10	Year 5	450
Sheet4 (2)

Cell Formulas
Range	Formula
B6	B6	=(B2+C2)/2
B7	B7	=(C2+D2)/2
B8	B8	=(D2+E2)/2
B9	B9	=(E2+F2)/2
B10	B10	=(F2+H2)/2

 

[onlyadrafter]

Hello,

one way in B6

=(OFFSET($B$2,0,ROW()-6)+OFFSET($B$2,0,ROW()-5))/2

and copy down as far as required

 

[Fluff]

Another option

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	ANNUAL AMOUNTS	YR1	YR2	YR3	YR4	YR5
2		500	300	400	600	900
3
4
5
6	Year 1	400
7	Year 2	350
8	Year 3	500
9	Year 4	750
10	Year 5	450
Sheet2

Cell Formulas
Range	Formula
B6:B10	B6	=SUM(INDEX($B$2:$G$2,SEQUENCE(2,,ROWS(B$2:B2))))/2

 

[EvansB2]

Hello,

one way in B6

=(OFFSET($B$2,0,ROW()-6)+OFFSET($B$2,0,ROW()-5))/2

and copy down as far as required

Thanks for this. I have tried it and works brilliantly. Thanks again.

Ben

 

[EvansB2]

Another option

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	ANNUAL AMOUNTS	YR1	YR2	YR3	YR4	YR5
2		500	300	400	600	900
3
4
5
6	Year 1	400
7	Year 2	350
8	Year 3	500
9	Year 4	750
10	Year 5	450
Sheet2

Cell Formulas
Range	Formula
B6:B10	B6	=SUM(INDEX($B$2:$G$2,SEQUENCE(2,,ROWS(B$2:B2))))/2

Thanks for this. I'll give it a go and I'm sure will save a lot of manual inputs!

Regards

Ben

 

[Fluff]

Glad we could help & thanks for the feedback.

One thing to be aware of, is that OFFSET is a volatile function & will re-calculate whenever you change any cell. Depending on the amount of data you have, this could cause your workbook to slow down.