CUME Week to date formula

[horkstar12]

Hello,

I have a table of data, in the first column is the week i.e Week 1, Week 2 etc. In the second column i have fully populated with last year sales data by week. in the final column i have this year data which every Monday is updated.

I have this setup for many departments and each week i have to go through each sheet and amend the CUME week to date sum.

For example we are currently on week 20 so i go into the cume SUM forumla for both last year and this year and move this down one to include week 20.

Ideally i want a formula to look at the THIS YEAR column and when this becomes populated to SUM from week 1 to that week. Formula will then be applied to the Last year and this year cume figures.

[TABLE="width: 500"]
<tbody>[TR]
[TD]CUME TO WEEK[/TD]
[TD]10'000'000[/TD]
[TD]8'000'000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WEEK 1[/TD]
[TD]250000[/TD]
[TD]150000[/TD]
[/TR]
[TR]
[TD]WEEK 2[/TD]
[TD]300000[/TD]
[TD]280000[/TD]
[/TR]
[TR]
[TD]WEEK 3[/TD]
[TD]400000[/TD]
[TD]350000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example above if this helps

Thanks

Drew

 

[pgc01]

Hi Drew

If I understand correctly you can always add all weeks in the current year.

If the week1 is in C3, something like:

=SUM(C3:C60)

For the last year you want to add just the weeks up to the last one populated from this year.
How are the values for the future weeks intialised? Are they initialised with 0 (zero) or are they empty (or something else)?

 

[horkstar12]

Hello,

Thanks for the reply.

The figures in the main body are more or less manual entry.

Its the CUME roll up at the top which i want to be dynamic enough to only count all the week to date figures. All the last year data is populated and this year the figures are added each week and i need to report week on week comparisons against last year.

Currently you are correct, in the CUME roll up the formula is just a simple sum from Week 1 down to Week 20 and then each week i add in the next week.

I want the formula to only count up to the current week so when i enter This weeks figure it will auto update the CUME roll up to include that figure.

Does that make sense?

Thanks

 

[Oeldere]

use a pivot table for that kind of work.

 

[pgc01]

Currently you are correct, in the CUME roll up the formula is just a simple sum from Week 1 down to Week 20 and then each week i add in the next week.

I want the formula to only count up to the current week so when i enter This weeks figure it will auto update the CUME roll up to include that figure.

Does that make sense?

Well, no.

If you add each week, either manually or automatically, for the current year I don't see why you have to use a dynamic formula.
You can just add the whole 50+ weeks. Like I posted, I'd use something like:

=SUM(C3:C60)

a simple, static formula. If for ex. you are in week 5, then your have values in C3:C7 and the rest of the cells in C8:C60 will be empty or with a 0 or with a null string, for ex. In any case the SUM() function will ignore them, since they are not numbers or will add the 0's with does not change the result.

Does this make sense to you?

Another thing is for the last year. There you just want to add the values up to the last week in this current year with a value. That's why I asked how are values for the future weeks of the current year initialised.

 

[pgc01]

For ex., with the values for the weeks of last year in B3, down, and the values for the weeks of the current year in C3, down, try for the sum of last year weeks:

=IF(COUNT(C3:C60)=0,0,SUM(B3:INDEX(B:B,LOOKUP(2,1/C3:C60,ROW(C3:C60)))))

 

[pgc01]

or, simpler:

=SUMPRODUCT(--ISNUMBER(C3:C60),B3:B60)

 

[horkstar12]

For ex., with the values for the weeks of last year in B3, down, and the values for the weeks of the current year in C3, down, try for the sum of last year weeks:

=IF(COUNT(C3:C60)=0,0,SUM(B3:INDEX(B:B,LOOKUP(2,1/C3:C60,ROW(C3:C60)))))

This is the exact formula i was looking for! thank you!

Looking to add in the week by week forecasts too, would i be able to flip this formula around to calculate the forecasted balance for the remaining weeks to come? So i could say from week 1 to week 20 is 8'000'000, we took 10'000'000 last year but in the remaining weeks of the half (week 21-26) we have an extra 4'000'000.

This figure would then reduce down as the 'this year' figure is entered?

 

[pgc01]

Hi

I don't understand your example.

Let's assume we have entered the values for weeks 1-10 of this year and we have the following totals

This year: Weeks 1-10: 11,000

Last year: Weeks 1-10: 10,000 - Weeks 11-26: 20,000

In this case we have an increase of 10% in the first 10 weeks of this year.

What you want for the first 26 is:

This year's weeks 1-10 + Last year's weeks 11-26 + 10%

=1100+20000*1.1=33000

Is this correct?

 

[horkstar12]

Hi

I don't understand your example.

Let's assume we have entered the values for weeks 1-10 of this year and we have the following totals

This year: Weeks 1-10: 11,000

Last year: Weeks 1-10: 10,000 - Weeks 11-26: 20,000

In this case we have an increase of 10% in the first 10 weeks of this year.

What you want for the first 26 is:

This year's weeks 1-10 + Last year's weeks 11-26 + 10%

=1100+20000*1.1=33000

Is this correct?

No, sorry.

What i mean is along side the current data, week by week i will enter the forecast weekly sales.

For the weeks still to come i.e 11-26 i want to total these up so essentially i have the actual cume to date figures (weeks 1-10) then the sum of the forecasted weeks ahead (11-26).

The formula previous will count all the cume to date on a week by week basis, the forecasted SUM will add up the totals for the remaining weeks as each week is met. Essentially next week CUM will be 1-12 then forecasted sum will be 13-26.

It would work off the same parameters as the previous formula, when This year is populated then sum the remaming forecasted sales

This is used to say how much of the season we have left to go after in forecasted sales.

Sorry for not being clear, hope that explains, i can post an example if it helps.