How to extrapolate bucketed values into pivot tabl

Question

I need to extrapolate my inventory at the item level into 4-week buckets between 0 and 52 weeks. I have about 4500 unique lines as follows:

 Item                               Unit   Weekly       
 Code      Description       Cases   Cost   Mvmnt    WOH    Bucket
 595384  PREMIUM FRYING OIL   500    $17.92    50     10   8-12 wks
 546760  DARK BROWN SUGAR     650    $11.81    10     65   >1 year
 598456  STEAM CORN           330    $15.42    15     22   20-24 wks
 532943  CHC SEMI SWEET       240    $34.13    80      3   <4 wks

The first item moves 50 cases a week, so the 500 cases in inventory represents 10 weeks on hand (WOH) and it falls into the 8-12 wks bucket. I want to show where this inventory falls, but it is inaccurate to say 500 cases of PREMIUM FRYING OIl fall into the 8-12 wks bucket. In reality 200 cases fall into <4 wks, 200 cases fall into 4-8 wks, and the remaining 100 cases fall into the 8-12 wks bucket. So I want to convert the first line into the following:

 Item                               Unit   Weekly       
 Code      Description       Cases   Cost   Mvmnt    WOH    Bucket
 595384  PREMIUM FRYING OIL   200    $17.92    50     10   <4 wks
 595384  PREMIUM FRYING OIL   200    $17.92    50     10   4-8 wks
 595384  PREMIUM FRYING OIL   100    $17.92    50     10   8-12 wks

...and repeat this for each item. Ultimately I want to turn this into a pivot table to sum all cases by bucket.

What I actually did was create 14 columns at the end of this data, one column for each 4-week bucket and wrote up a formulas to create running subtraction. This worked, but is time consuming and difficult to repeat, hence I am looking for a different solution. Please let me know if I need to clarify anything better.

Answer 1

Might get you started, or clarify your requirement, assuming Item Code is in A1:

in I2: =4*E2 (the 4-weekly movement)
in J2: =INT(C2/I2) (the number of rows at the full 4-weekly rate)
in K2: =I2*(C2/I2-INT(C2/I2)) (the value of an extra row, if required)

all three formulae copied down to suit.

But it seems you may be better off pivoting your source data and adding calculations in the resulting table.

I particular I do not understand "buckets between 0 and 52 weeks" in conjunction with ">1 year" and I don't see what/why "extrapolation" (maybe interpolation?).