Results 1 to 9 of 9
Thread: summing Data

20100219, 14:12 #1
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
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

20100219, 14:51 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20100219, 15:53 #3
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
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 AI13the 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

20100220, 00:16 #4
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
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/01Which 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:ANthese will change each time a new column is inserted each month
The number of month Traded is depicted in N6
Your assistance will most appreciated
Regards
Howard

20100220, 07:25 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20100220, 07:55 #6
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 7,999
 Thanks
 46
 Thanked 954 Times in 885 Posts
I think this is what you want. (Kudos to Steve for the idea.)
=SUM(OFFSET(AI13,0,0,1,N6))
cheers, Paul

20100220, 08:03 #7
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
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

20100220, 08:17 #8
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 7,999
 Thanks
 46
 Thanked 954 Times in 885 Posts
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

20100220, 11:58 #9
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
Hi Paul
Thanks for the explanation
Regards
Howard