I understand your point and thought as much in the first post, only
wondered whether a simpler approach would do.

The new approach basically searches until we get to a month where
inventory will not be enough. Then it divides the remainder by the
inventory amount. To do this I modify the layout somewhat. A1:D1 still
contain forecasts. Now in A2:

=SUM($A$1:A1)

Copy to the right to produce cumulative consumption. Now it is cell A3
that contains the inventory. The following formula, for your data,
returns 3.078431373

=IF(ISNUMBER(MATCH(A3,A2:D2,0)),MATCH(A3,A2:D2,0), MATCH(A3,A2:D2)+(A3-
INDEX(A2:D2,MATCH(A3,A2:D2)))/INDEX(A1:D1,MATCH(A3,A2:D2)+1))

Philosophy: If it so happens that the inventory is consumed exactly at
the end of a month then the formula returns exactly the number of
months. Otherwise the formula finds the last month that inventory was
not exceeded. Then it adds the ratio of unconsumed divided by the
forecast of the next month.

Does this help?
Kostis

On May 20, 8:26 pm, Theawilla
wrote:
This is really close but is there some way to do it without averaging? I
have 52 weeks of forecast data and this would work if the weeks were all
pretty even, but doesn't account for huge spikes during
Thanksgiving/Christmas.

This would work if I just counted one week past my total on hands and only
used those weeks but I would have to do that for every week instead of just
copying the formula.

Thanks so much for your post.

"vezerid" wrote:
A simple approach would be to divide 312 with the average consumption
per week, hence in A3:

=A2/AVERAGE(A1:D1)

With your numbers as input the formula returns: 3.159493671

Does this help?
Kostis Vezerides

On May 20, 7:18 pm, Theawilla
wrote:
Excel 2003

Hello, I am trying to calculate weeks on hand.

I have a forecast for an item by week (A=week1, B=week2, etc) I also have
the total quantity on hand. I need a formula that will calculate how many
cells (weeks) = the total quantity on hand. Example:
A1=99; B1=105; C1=89; D1=102 (forecasted quantities)
A2=312 (quantity on hand)
A3=3.2 (weeks on hand- rounded to tenths)- this is where I need the
formula.

Thanks in advance for any help given.