Results 1 to 8 of 8
Thread: moving formula (Excel 97)

20010626, 06:08 #1
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
moving formula (Excel 97)
Hi all,
I have a spreadsheet (see a small example of it attached) where the numbers in my table (for each month of this year) are linked to another sheet. The last two columns are called Past 3 and Past 6, which basically add up the last three months with data in them. Ex., if we are now in June, the Past 3 should add up the numbers from April to June. Next months should move from May to July. The same thing applies for the Last 6, but should include 6 months instead of 3. The summing formula is nothing, however I cannot seem to get it to 'move' along with the new data that comes every months.
Does anybody have some ideas on how to solve this problem?
Thanks a lot,
K.

20010626, 12:12 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
I believe that these two formulas will do what you want:
<pre>=SUM(OFFSET(A3,0,MAX(0,MONTH(NOW())3)):OFFSET(A3,0,MONTH(NOW())1))
=SUM(OFFSET(A3,0,MAX(0,MONTH(NOW())6)):OFFSET(A3,0,MONTH(NOW())1))
</pre>
I am attaching your workbook modified to use these formulas.Legare Coleman

20010626, 12:54 #3
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
Thanks a lot for your help. It does work, however, due to my omitting of some things, it works only halfways. I simply didn't realise you would use the monthnow combination.
What actually happens, is that we have also socalled bimonthly data, for which the description of the months (in thr row 1) look like this: JF01, MA01, MJ01, JA01, SO01, ND01. Now here your formula will not work, as we are not speaking of real months in each columns.
Is there any possibility to modify the formula to work with these labels for the periods as well?
I appologise again for not mentioning this before, I feel really bad about it.
Thanks,
K.

20010626, 13:01 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
What do you want to sum in that case. I don't see how its possible to pick three months of data when two months are combined. six months and three months are both a problem when the current month is an even numbered month. You will need to define what you want to sum.
Legare Coleman

20010626, 14:36 #5
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
Ok, try not to think of the descriptions of the months as such, but rather as any descriptions. In our company we are mainly working with bimonthly data, less with monthly data. We are pulling the data from a databank created specially for our needs. Therefore when I say Past 3, I mean past 3 bimonthly periods, not the past 3 physical periods, i.e. if we are now in the MJ01 period (MayJune data collection), then I want the data to be cummulated from JF01 (i.e. 3 cells starting from JF01). If we are the next time in JA01, I want it to be summed up from MA01 onwards, always three columns. I am not sure how clear is this concept, that is why I simplified it in my first post, so I wouldn't have to explain all this, as not many people are used to bimonthly data retrieval.
I kind of believe that what I want is not possible, unless there is a way to tell in the formula to sum up the last three cells where there is data but > 0, since the 0 is because the data is linked to an empty cell in the other worksheet.
Am I confusing enough?
[img]/forums/images/smilies/smile.gif[/img]
Regards,
K.

20010626, 14:56 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
OK, see if these formulas will work:
<pre>=SUM(OFFSET(A3,0,MAX(0,MATCH(0,A3:L3,0)4)):OFFSET(A3,0,MATCH(0,A3:L3,0)1))
=SUM(OFFSET(A3,0,MAX(0,MATCH(0,A3:L3,0)7)):OFFSET(A3,0,MATCH(0,A3:L3,0)1))
</pre>
Those will fail when the Dec data (in you first example) is filled in. To make it work, you will need to add a column after the last column of data and fill it with zeros. You can then hide that column.Legare Coleman

20010627, 04:54 #7
 Join Date
 Feb 2001
 Posts
 107
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
Well, I am not sure how you actually did in the formula, as much as I try to understand it  I can't, but it does work!. I added an extra column, filled it with 0, and in the formula I selected it as well (ex. instead of A3:L3 I took now A3:M3).
Thanks a lot for making the impossible possible.
K.

20010627, 19:34 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: moving formula (Excel 97)
The difficult we do immediately. The impossible may take a little longer. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
Legare Coleman