Offset formula help

[tealy1980]

Hi all,

I have a quick question about the offset formula, pretty basic I think nut I'm a novice!

So I have a spreadsheet, summarised like so:

I want to sum the weeks by period, is there anyway to use offset so when I drag the formula across it wont just move one column at a time, but rather by 4 weekly blocks??

Many thanks in advance.

p1	p2	p3	p4
4	8	xxx	xxx
week 1	week 2	week 3	week 4	week 5	week 6	week 7	week 8
1	1	1	1	2	2	2	2

<tbody>
</tbody>

 

[ukmikeb]

Hi

Try :-

=SUM(OFFSET($A$5,,(COLUMNS($A:A)-1)*4,1,4))

hth

 

[AndyPandy27]

Hi tealy1980,

Assuming:

A1:D1 = Your Period Headings (P1, P2, P3, P4)
A2:D2 = [The OFFSET Formula you need]
A3:P3 = Your Week Headings (Week 1, Week 2 etc)
A4:P4 = Your Week values

In A2 enter the array formula:

=SUM(OFFSET($A$1,3,(VALUE(RIGHT(A1,LEN(A1)-1))*4)-4,1,4))

(Make sure to use CTRL+SHIFT+ENTER)

Then copy across from A2 to D2

This worked for me - good luck.

Cheers,

Andy

 

[AndyPandy27]

Hi tealy1980,

Assuming:

A1:D1 = Your Period Headings (P1, P2, P3, P4)
A2:D2 = [The OFFSET Formula you need]
A3:P3 = Your Week Headings (Week 1, Week 2 etc)
A4:P4 = Your Week values

In A2 enter the array formula:

=SUM(OFFSET($A$1,3,(VALUE(RIGHT(A1,LEN(A1)-1))*4)-4,1,4))

(Make sure to use CTRL+SHIFT+ENTER)

Then copy across from A2 to D2

This worked for me - good luck.

Cheers,

Andy

 

[AndyPandy27]

Hi tealy1980,

Assuming:

A1:D1 = Your Period Headings (P1, P2, P3, P4)
A2:D2 = [The OFFSET Formula you need]
A3:P3 = Your Week Headings (Week 1, Week 2 etc)
A4:P4 = Your Week values

In A2 enter the array formula:

=SUM(OFFSET($A$1,3,(VALUE(RIGHT(A1,LEN(A1)-1))*4)-4,1,4))

(Make sure to use CTRL+SHIFT+ENTER)

Then copy across from A2 to D2

This worked for me - good luck.

Cheers,

Andy

 

[AndyPandy27]

Apologies all...

My computer appears to have gone a bit crazy, hence the multiple, identical replies. I tried to find a way to delete the superfluous ones, but was unsuccessful - sorry.

I also hadn't realised that ukmikeb had already replied, so apologies again!

Andy

 

[tealy1980]

Hi guys, thanks for the quick replies, appreciate it.

Andy, I tried yours however it returns #value, when I evaluate it it's in the period row that causes the error, not sure why!

Thanks

 

[tealy1980]

Mike, is a5 the cell where the formula goes? I'm getting 0 in the cells when I paste in here.

Can I attach a spreadsheet to these forums?

Period 1	Period 2	Period 3	Period 4
#VALUE!	#VALUE!	#VALUE!	#VALUE!
week 1	week 2	week 3	week 4	week 5	week 6	week 7	week 8
1	1	1	1	2	2	2	2
=SUM(OFFSET($A$5,,(COLUMNS($A:A)-1)*4,1,4)) <colgroup><col width="64"></colgroup><tbody> </tbody>	0	0	0	0	0

<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>
</tbody>

Thanks

 

[AndyPandy27]

Andy, I tried yours however it returns #value, when I evaluate it it's in the period row that causes the error, not sure why!

Hi tealy1980,

My answer requires your Period headings to be "P1", "P2" etc (as per your original post) - I see from your latest post that this has changed to "Period 1", "Period 2" etc...

If you changed to the "P1", "P2" version, would my one then work?

Or, if you wanted to keep it as "Period 1" etc, then you could change my one to:

=SUM(OFFSET($A$1,3,(VALUE(RIGHT(A1,LEN(A1)-7))*4)-4,1,4))

Andy

 

[tealy1980]

Andy thats perfect!! thank you very much. My stupidity got the better of me.