Offset formula help

tealy1980

New Member
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??

 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>

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi

Try :-
Code:
``=SUM(OFFSET(\$A\$5,,(COLUMNS(\$A:A)-1)*4,1,4))``

hth

Hi tealy1980,

Assuming:

A2:D2 = [The OFFSET Formula you need]

In A2 enter the array formula:

Code:
``=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

Hi tealy1980,

Assuming:

A2:D2 = [The OFFSET Formula you need]

In A2 enter the array formula:

Code:
``=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

Hi tealy1980,

Assuming:

A2:D2 = [The OFFSET Formula you need]

In A2 enter the array formula:

Code:
``=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

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.

Andy

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

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 1Period 2Period 3Period 4
#VALUE!
#VALUE!#VALUE!#VALUE!
week 1week 2week 3week 4week 5week 6week 7week 8
11112222
 =SUM(OFFSET(\$A\$5,,(COLUMNS(\$A:A)-1)*4,1,4))

<colgroup><col width="64"></colgroup><tbody>
</tbody>
00000

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

Thanks

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:

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

Andy

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

Replies
1
Views
159
Replies
2
Views
246
Replies
1
Views
67
Replies
8
Views
289
Replies
1
Views
103

1,196,073
Messages
6,013,269
Members
441,758
Latest member
Abren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back