Calculating back until certain sum is reached

[SebJuno]

Hi all,

First time here, not sure if this is a simple problem that I just can't figure out how to solve.

For those familiar with the messaging platform Slack, I am trying to forecast/estimate as my organisation uses the platform, how far back our 10,000 message searcg limit will serve us. For this, I have entered in our current weekly number of messages sent and forecast this increasing number going forward. What I am trying to find out is at each week going forward, how many weeks can we go back before we reach our 10,000 cap.

So each period is 1 week and has a value of the number of messages sent. Going down the list at each period, I need to know how far back I need to go before I reach the cap.

Example Data Set


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Week Starting[/TD]
[TD]Messages Sent[/TD]
[TD]Forecast[/TD]
[TD]Search (# of Wks)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30/9[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7/10[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14/10[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]21/10[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]28/10[/TD]
[TD][/TD]
[TD]2500[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4/11[/TD]
[TD][/TD]
[TD]3000[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11/11[/TD]
[TD][/TD]
[TD]3500[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]18/11[/TD]
[TD][/TD]
[TD]4000[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]25/11[/TD]
[TD][/TD]
[TD]4500[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully that makes sense. Appreciate any help.

Thanks

 

[Menumorut]

Hey now,

I don't really understand what you are trying to do, I mean I don't know if you have a 10000 cap/week or in total.
Anyway, solution is similar, please see below the two tables attached (I inverted columns and rows cause it's easier for me). Option one is for 10000/week, option two is for a total cap of 10000.

[TABLE="class: grid, width: 1704"]
<colgroup><col><col span="20"></colgroup><tbody>[TR]
[TD]Option 1
[/TD]
[TD]Week Num[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Start day
[/TD]
[TD="align: right"]23-Oct[/TD]
[TD="align: right"]30-Oct[/TD]
[TD="align: right"]6-Nov[/TD]
[TD="align: right"]13-Nov[/TD]
[TD="align: right"]20-Nov[/TD]
[TD="align: right"]27-Nov[/TD]
[TD="align: right"]4-Dec[/TD]
[TD="align: right"]11-Dec[/TD]
[TD="align: right"]18-Dec[/TD]
[TD="align: right"]25-Dec[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]15-Jan[/TD]
[TD="align: right"]22-Jan[/TD]
[TD="align: right"]29-Jan[/TD]
[TD="align: right"]5-Feb[/TD]
[TD="align: right"]12-Feb[/TD]
[TD="align: right"]19-Feb[/TD]
[TD="align: right"]26-Feb[/TD]
[/TR]
[TR]
[TD]Message sent
[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Forecast
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]8500[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]9500[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]8500[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]9500[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]Weeks left[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]4.50[/TD]
[TD="align: right"]3.20[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]1.63[/TD]
[TD="align: right"]1.33[/TD]
[TD="align: right"]1.10[/TD]
[TD="align: right"]0.91[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.62[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]0.40[/TD]
[TD="align: right"]0.31[/TD]
[TD="align: right"]0.24[/TD]
[TD="align: right"]0.17[/TD]
[TD="align: right"]0.11[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 1704"]
<colgroup><col><col span="20"></colgroup><tbody>[TR]
[TD]Option 2
[/TD]
[TD]Week Num[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Start day[/TD]
[TD="align: right"]23-Oct[/TD]
[TD="align: right"]30-Oct[/TD]
[TD="align: right"]6-Nov
[/TD]
[TD="align: right"]13-Nov[/TD]
[TD="align: right"]20-Nov[/TD]
[TD="align: right"]27-Nov[/TD]
[TD="align: right"]4-Dec[/TD]
[TD="align: right"]11-Dec[/TD]
[TD="align: right"]18-Dec[/TD]
[TD="align: right"]25-Dec[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]15-Jan[/TD]
[TD="align: right"]22-Jan[/TD]
[TD="align: right"]29-Jan[/TD]
[TD="align: right"]5-Feb[/TD]
[TD="align: right"]12-Feb[/TD]
[TD="align: right"]19-Feb[/TD]
[TD="align: right"]26-Feb[/TD]
[/TR]
[TR]
[TD]Message sent[/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Forecast[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]4500
[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]8500[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]9500[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]6500[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]8500[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]9500[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]Weeks left[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]3.50[/TD]
[TD="align: right"]2.20[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.63[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]0.10[/TD]
[TD="align: right"]-0.09[/TD]
[TD="align: right"]-0.25[/TD]
[TD="align: right"]-0.38[/TD]
[TD="align: right"]-0.50[/TD]
[TD="align: right"]-0.60[/TD]
[TD="align: right"]-0.69[/TD]
[TD="align: right"]-0.76[/TD]
[TD="align: right"]-0.83[/TD]
[TD="align: right"]-0.89[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

First cell in "weeks left" has this formula: =$U$32/C32. For the second cell in the row use the options:

Formulas for option 1 in "Weeks left" - =($U$32-C32)/D32. Drag formula to fill cells
Formula for option 2 in "Weeks left" - =($U$32-SUM(C40:D40))/D40. Drag formula to fill cells

Here for you if you have questions.

Cheers,
Emil

 

[SebJuno]

Hi Emil,

Thanks for the reply. This is on the right track but the results are not quite right.

I'll try and articulate how the cap works. The searchable messaging cap is 10,000 messages total starting at the most recent. Each week, the number of messages adds to the total number of messages sent, but only the most recent 10,000 are searchable. So for example, at week 46, the total sum of messages sent are 9000. So all 5 weeks would be searchable. The following week would then be the first one that breaches the cap, with the total going over some time in week 44-45. As we progress, with increasing numbers of messages each week, this would then reduce the number of weeks searchable further.

Hopefully that explains it a bit clearer.

Seb

 

[Menumorut]

So in week 47, since you would have reached the 10000 cap, could you be able to search only that week or basically weeks 45 to 47 (as they have 9000 combined and that is below 10000)?

 

[SebJuno]

So in week 47, since you would have reached the 10000 cap, could you be able to search only that week or basically weeks 45 to 47 (as they have 9000 combined and that is below 10000)?

It would be week 45-47.

The way that I imagine it is calculated is that the formula starts at the given week, sees how many messages have been sent. If below 10,0000 then adds the previous week, if still below 10,000 move on and adds the week before that to a cumulative total etc etc until it reaches 10,000. Then returns the number of how many periods it had to go back before that cap was reached.