Tricky Excel Problem

[Borscht Mat]

Howdy,

Need some help from you smart young things.

I own a brewery, and I am doing some forward production planning. I have developed a model which looks at each of my tanks, and projected sales for each day of the year and it models when each tank will run dry and therefore when I need to brew some more beer, and into which tank, how much, which type etc.

What I want to be able to do is to set as a variable the number of days I look ahead in my sales forecasts to see when the tank will run dry. For example, if I am making a Pale Ale I might need to brew a new batch 14 days before the tank runs dry to give enough time for the beer to ferment properly, but for a Pilsner Lager I might want to look ahead 28 days because it needs a longer time.

I have the daily sales forecasts for the year in Column A. Presently if I want to look 14 days ahead I simply do a sum from today plus 14 days. The problem with this is that the "14" is fixed in the formula, and if I want to change it I must go in, change the formula, and copy it down etc..... there are about 10 places I need to change this so it is a pain in the arse.

My thought was to switch to R1C1 mode and then if I could reference the sum range as R[1]C[1]:R[15]C[1] or something like that. This is again easy if the 14 (15 is 14 plus 1) is fixed. What I need to do is R[1]C[1]:R[1+$B$16]:C[1] and then I can set B16 to 14, 21, 852 whatever and it will give me the sum of the correct number of cells, however this gives an error (of course I enter $b$16 in the correct R1C1 format).

Free beer to anyone who can help, you just need to come to Slovenia to pick it up

just as an example if I have the following daily sales

7
6
13
52
8
7
6
9
5
3

in another cell I have the number of days I want to count in this case say

5

then in the third cell I want the sum of the first 5 numbers. Then if I change the 5 to a 7 it gives the sum of the first 7 numbers.



cheers

 

[Ferris_rules]

hmmmm thats so late in the night and im so tired lol

But your on the right path in terms of referencing !

Maybe use another worksheet and make reference to that to make things uncomplicated.

Hmmmm if I have time might gave a proper bash at helping you, I think the problem is easier than what your saying its just that to word it sounds complicated.

 

[Ferris_rules]

This may help but google for excel worksheets, i often find plenty of examples that I just manipulate formulas to get what I want it to do, but it forms a good template using others work !

 

[Ad76]

Howdy,

Need some help from you smart young things.

I own a brewery, and I am doing some forward production planning. I have developed a model which looks at each of my tanks, and projected sales for each day of the year and it models when each tank will run dry and therefore when I need to brew some more beer, and into which tank, how much, which type etc.

What I want to be able to do is to set as a variable the number of days I look ahead in my sales forecasts to see when the tank will run dry. For example, if I am making a Pale Ale I might need to brew a new batch 14 days before the tank runs dry to give enough time for the beer to ferment properly, but for a Pilsner Lager I might want to look ahead 28 days because it needs a longer time.

I have the daily sales forecasts for the year in Column A. Presently if I want to look 14 days ahead I simply do a sum from today plus 14 days. The problem with this is that the "14" is fixed in the formula, and if I want to change it I must go in, change the formula, and copy it down etc..... there are about 10 places I need to change this so it is a pain in the arse.

My thought was to switch to R1C1 mode and then if I could reference the sum range as R[1]C[1]:R[15]C[1] or something like that. This is again easy if the 14 (15 is 14 plus 1) is fixed. What I need to do is R[1]C[1]:R[1+$B$16]:C[1] and then I can set B16 to 14, 21, 852 whatever and it will give me the sum of the correct number of cells, however this gives an error (of course I enter $b$16 in the correct R1C1 format).

Free beer to anyone who can help, you just need to come to Slovenia to pick it up

just as an example if I have the following daily sales

7
6
13
52
8
7
6
9
5
3

in another cell I have the number of days I want to count in this case say

5

then in the third cell I want the sum of the first 5 numbers. Then if I change the 5 to a 7 it gives the sum of the first 7 numbers.


cheers

Thanks mate, if I'm ever in Slovenia I'll look you up

I'm sure there probably is a way to do it dynamically like you suggested but without research this is the simplest solution i have:

Add 2 extra columns, "sum" and "Days" so you now have 3.

actual	sum	days
7	7	1
6	13	2
13	26	3
52	78	4
8	86	5
7	93	6
6	99	7
9	108	8
5	113	9
3	116	10

Sum is a running sum of everything in your projections column
Days is there for your lookup.

Then the formula that gets yu your value is this: =LOOKUP(H4,E4:E13,D4: D13)
Where H4 is the value of the cell that you're looking up, the first range is the days column and the second range is the sum column.

So in this case putting the value 5 in H4 would return you 86 which if i understood you correctly is what you were looking for.

 

[Rod Stroker]

Done. Is this what you're looking for? Cell C3 is a toggle switch where you can change the number of days to be counted, then the resulting output is displayed in cell E13.

If this is what you want then send me your email address and I'll forward you the excel file.

I do this sort of stuff every day, although on a more complex scale

 

[Borscht Mat]

cheers all.... Bigfooty always reliable.

Indeed there is a function "OFFSET"

So if I use sum(a1 : offset(a1,b23,1))

So if b23 has say a value of 53....

then it dynamically defines the range as being from A1 to "53 rows down from A1 and 1 column across.... or you can replace "1" with h45 and make it a 2x2 range)

kinda neat

BM