awbcollier
New Member
- Joined
- Apr 20, 2010
- Messages
- 2
Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
I have been trying to figure out a way to do this, and so far have failed miserably. I can't seem to develop an array function that works, and my VBA is not up to scratch enough to create this, although I think a loop function might do what I need.<o></o>
<o></o>
I have a row of data that holds dispatch values by week. I have a second row of data that holds stock levels by week. I simply want to create a third row that tells me how many weeks of demand a stock level will cover.<o></o>
<o></o>
The data looks like this (comma deliminated):<o></o>
<o></o>
Week, 1, 2, 3, 4, 5, 6, 7, 8, 9<o></o>
Stock, 26490, 24490, 22490, 20490, 18490, 14239, 17898, 13646, 9393<o></o>
Dispatch, 2000, 2000, 2000, 2000, 4251, 4251, 4252, 4253, 2350<o></o>
<o></o>
In this example week 1 would have 9 weeks of stock cover, week 2 - 8 weeks and week 3 - 7 weeks of cover.<o></o>
<o></o>
This is calculated by summing along the dispatch row until the cumulative sum of dispatch exceeds the current week’s stock level; then counting the number of weeks in the sum that cumulatively are less than (but closest to without being over) the total stock of that week.<o></o>
<o></o>
Currently we manually create a massive array that sums along up to 26 weeks in advance and then have a manual formula that finds the maximum value below the target. This then outputs the column reference and then calculates the number of weeks based on the difference between the week’s column and the column highlighted.<o></o>
<o></o>
This is massively inefficient and takes far too much time and makes files too large to email. I am sure this can be done with a formula, array or Dsum (maybe vba) but cannot figure it out. Any help greatly appreciated.<o></o>
fficeffice" /><o></o><o
></o>I have been trying to figure out a way to do this, and so far have failed miserably. I can't seem to develop an array function that works, and my VBA is not up to scratch enough to create this, although I think a loop function might do what I need.<o
></o><o
></o>I have a row of data that holds dispatch values by week. I have a second row of data that holds stock levels by week. I simply want to create a third row that tells me how many weeks of demand a stock level will cover.<o
></o><o
></o>The data looks like this (comma deliminated):<o
></o><o
></o>Week, 1, 2, 3, 4, 5, 6, 7, 8, 9<o
></o>Stock, 26490, 24490, 22490, 20490, 18490, 14239, 17898, 13646, 9393<o
></o>Dispatch, 2000, 2000, 2000, 2000, 4251, 4251, 4252, 4253, 2350<o
></o><o
></o>In this example week 1 would have 9 weeks of stock cover, week 2 - 8 weeks and week 3 - 7 weeks of cover.<o
></o><o
></o>This is calculated by summing along the dispatch row until the cumulative sum of dispatch exceeds the current week’s stock level; then counting the number of weeks in the sum that cumulatively are less than (but closest to without being over) the total stock of that week.<o
></o><o
></o>Currently we manually create a massive array that sums along up to 26 weeks in advance and then have a manual formula that finds the maximum value below the target. This then outputs the column reference and then calculates the number of weeks based on the difference between the week’s column and the column highlighted.<o
></o><o
></o>This is massively inefficient and takes far too much time and makes files too large to email. I am sure this can be done with a formula, array or Dsum (maybe vba) but cannot figure it out. Any help greatly appreciated.<o
></o>
