1. I need to place a formula in N13, that will add the values 12 columns to the right of X13 and the X number of columns to the right based on the Value in cell N6 -1

A new column is inserted each month where column X is, so I thought that the offset or Index function would be most appropriate, but not sure how to use this in this instance

See sample data using sum formula

Your assistance will be most appreciated

2. I am not sure I understand? Could you be a little more specific. I think that the SUM with the OFFSET function based on 12 and the N6 value, will be what you need but I am not sure exactly what you are after to provide the formula.

Steve

3. Originally Posted by sdckapr
I am not sure I understand? Could you be a little more specific. I think that the SUM with the OFFSET function based on 12 and the N6 value, will be what you need but I am not sure exactly what you are after to provide the formula.

Steve
Hi Steve

In this example I would like to sum start summing 11 columns to the right of X13 (not 12 columns as per my previous posting i.e. AI13.Al13 (i.e an additional 4 columns to the right of AI13-the additional columns to the right of AI13 is determined by the Value in N6)

Each month an additional column will be inserted in Column X, so the formula will always sum 11 columns to right of X13

Your assistance will be most appreciated

Regards

Howard

4. Originally Posted by sdckapr
I am not sure I understand? Could you be a little more specific. I think that the SUM with the OFFSET function based on 12 and the N6 value, will be what you need but I am not sure exactly what you are after to provide the formula.

Steve

Hi Steve

I have tried to give you a clearer explanation below, so ignore previous comments

I need some help with a Formula

The financial Period runs from Oct to Sept for each Year

The current years data is contained in Columns R, Z, AA, & AB.

Each Month a new column will be inserted where column Z is, and the data from column R (10/01-Which is Jan 2010) will then be copied into column Z). The new months data for eg Feb 10/02 will then be imported into column R

I new formula that will add the previous periods data i.e the previous years data i.e these appear currently in columns AK:AN-these will change each time a new column is inserted each month

The number of month Traded is depicted in N6

Regards

Howard

5. You seem to keep changing the offset. This formula seems to work:
=SUM(OFFSET(X13,0,13,1,N6))

It sums the range starting 0 rows and 13 columns away from X13 (= AK13). The size of the range is 1 row and the number of columns in N6 (=4 in your example). Thus it sums the range AK13:AN13.

Is this what you are after?

Steve

6. I think this is what you want. (Kudos to Steve for the idea.)
=SUM(OFFSET(AI13,0,0,1,N6))

cheers, Paul

7. Hi Paul

Thanks for the help.

This formula works perfectly.

What does the 1 before N6 represent in the formula =SUM(OFFSET(AI13,0,0,1,N6)) ?

Regards

Howard

8. How it works
The range is calculated using AI13 as the starting point.
Offset 0 colums to begin the range.
Offset 0 rows to begin the range.
Range height (number of rows) = 1
Range length (number of columns) = N6

cheers, Paul

9. Hi Paul

Thanks for the explanation

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•