moving formula (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

I believe that these two formulas will do what you want:

=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))

I am attaching your workbook modified to use these formulas.

Reply | Quote

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 month-now combination.
What actually happens, is that we have also so-called bi-monthly 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.

Reply | Quote

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.

Reply | Quote

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 bi-monthly 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 bi-monthly periods, not the past 3 physical periods, i.e. if we are now in the MJ01 period (May-June 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?

Regards,
K.

Reply | Quote

OK, see if these formulas will work:

=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))

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.

Reply | Quote

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.

Reply | Quote

The difficult we do immediately. The impossible may take a little longer.

Reply | Quote