mtjanousek
New Member
- Joined
- Jul 25, 2018
- Messages
- 17
Hello all,
I ran into a problem with my excel file.
I am trying to calculate when I will need to add some additional quantity of material, but the table I am taking data from is not exactly helping.
Fig. 1 shows how the data are approximately displayed.
You can see production for each weak and year and also stock which is slowly going down.
The second table (Fig. 2) is trying to use the data from Fig. 1 and find out, based on prescribed coverage of weeks, how much quantity and starting when has to be produced.
I tried to use VLOOKUP but that does not seem feasible at all. Also MATCH and OFFSET. I need to get an equation which is able to:
Do you have an idea in what way it would be possible to go? Or do I need to transform the data into some different format using an additional excel sheet?
I ran into a problem with my excel file.
I am trying to calculate when I will need to add some additional quantity of material, but the table I am taking data from is not exactly helping.
Fig. 1 shows how the data are approximately displayed.
You can see production for each weak and year and also stock which is slowly going down.
The second table (Fig. 2) is trying to use the data from Fig. 1 and find out, based on prescribed coverage of weeks, how much quantity and starting when has to be produced.
I tried to use VLOOKUP but that does not seem feasible at all. Also MATCH and OFFSET. I need to get an equation which is able to:
- Find matching material number
- Take a look when the stock gets negative
- Associate with that information number of week (a number of week before it gets negative) and a year
- Calculate how much quantity is needed to cover number of weeks given in the "Coverage"
- And write that all down (the yellow cells)
Do you have an idea in what way it would be possible to go? Or do I need to transform the data into some different format using an additional excel sheet?
