OFFSET

[niting]

Hie forum,


By looking at lot of use of offset formulae in the forum, i tried using one for one of my workings. The data is Month wise with 4 subheadings in each month. I then go on to create a summary sheet with months in rows and 2 subheading amongst the 4 in the master data. I am using the formula like dis-:


(SUMPRODUCT((OFFSET(B$5,,N56,38))*(OFFSET(C$5,,N56,38))))/100000


However, I am using a helper column N which lists down the multiple of 4 whereby the offset formula picks the number relevant to that month with first entries in B5 and C5.


is there a cleaner way to execute the same without using helper column???


Thanks

NitinG

 

[xld]

Can you post some sample data so we can see what it looks like?

 

[niting]

Hi XLD,


Th data is like below-:


APRIL MAY JUNE ----

B C D E F G H I J K L M-------COLUMNS

20 200 10 30 30 300 20 40 40 400 30 50


I create a summary like dis-:


B B*C

April SUM(OFFSET(B$5,,N52,37))

May SUM(OFFSET(B$5,,N53,37))

June


The columns B*C contains the formula as referred to in original post. The column N52:N60 is the helper column with multiples of 4


I hope I have explained the problem. Would appreciate if the forum could suggest a more efficient way of using the offset formula.


Thanks

 

[Luke M]

Niting,

If I understand correctly, col N contains multiples of 4, (4, 8, 12, etc). This is because your data is based on 4 columns. To do this in 1 "master" formula, we can do something like:

=(OFFSET(B$5,ROW(A1)*4,38)*OFFSET(C$5,ROW(A1)*4,38))/100000


Note that this formula starts with a 4 row offset. If your first formula needs to start in row 5 (no offset), modify with:

(ROW(A1)-1)*4


Similarly, if you need to start at some odd amount like 1 offset, 2, offset, change to something like:

=ROW(A1)-1)*4-3