MrExcel Publishing
Your One Stop for Excel Tips & Solutions

help!! THIS IS WAY TO EAST TO BE DIFFICULT


Posted by Vinnie on September 13, 2001 12:44 PM

hey can someone please help me out, I have been working on this for the last 3 days, and have come to no conclusions only dead ends. My delema is this. The company I work for has 8 differant type of product we'll call them product 1 - 8. When our clients order from us idealy we want to be able to put the clients order amount in, then have excell evenly distribute accross our product line until it has givin one of each product before they get two of any, then it will after they have gotten 1 of each. Our product is sold in packs of 6 apples, so Here is a example

if client orders 6apples(Which is one order) he gets of our 1st product which is one type of 6 apples. If he order 12(two orders) he gets 1 of "product1" and 1 of "product2". This can continue for all the way up to 150 orders of 6. I have figured out the formula to use, but unfortunantly excell will only allow 7 formulas in a cell. this is how it should look

product-1-2-3-4-5-6-7-8
6 apples 6 0 0 0 0 0 0 0
12 apples 6 6 0 0 0 0 0 0
18 apples 6 6 6 0 0 0 0 0
24 apples 6 6 6 6 0 0 0 0 etc.

after they have gotten one of each product it starts over. Example

54 apples 12 6 6 6 6 6 6 6
60 apples 12 12 6 6 6 6 6 6
66 apples 12 12 12 6 6 6 6 6
72 apples 12 12 12 12 6 6 6 6
etc.

please help me out!!! I know this sounds complicated as hell but I know there is a easy way to do it.

thank you

vinnie


Posted by Aladin Akyurek on September 13, 2001 1:52 PM

Winnie,

I'll assume that each order is a multiple of 6 (e.g.,36 but not 35 or 37).

In A1 enter: the order amount.
In B1to I1 enter the series 1,2,3,4,5,6,7,8
In A2 enter: =IF((ROW()-1)*6<=$A$1,(ROW()-1)*6,"")
In B2 enter: =IF(ISNUMBER($A2),(ROW()-1>=B$1)*$A$2,"")
Copy the formula in B2 first to I2. Select the range A2:I2 then xopy down as far as you need.

Aladin

============