Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
stock control question
Hello all, I have 2000 items and want to represent them as full cartons -
inner cartons and single items. Full cartons have 36 pieces inner cartons have 6 pieces and singles are self explanatory. I tried using the INT formula but I can't seem to get it to work. Is there something else I can use? So want it to look like this stock 2000 - master cartons 55 ( 55x36=1980) inner cartons 3 ( 3 x 6=18) singles 2 ( 2 x 1) regards -- kinsey |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
stock control question
Assuming 2000 is in A1, all below formulas will go A2 - A4
=INT(A1/36) =INT(MOD(A1,36)/6) =A1-(A2*36+A3*6) "kinsey" wrote: Hello all, I have 2000 items and want to represent them as full cartons - inner cartons and single items. Full cartons have 36 pieces inner cartons have 6 pieces and singles are self explanatory. I tried using the INT formula but I can't seem to get it to work. Is there something else I can use? So want it to look like this stock 2000 - master cartons 55 ( 55x36=1980) inner cartons 3 ( 3 x 6=18) singles 2 ( 2 x 1) regards -- kinsey |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
stock control question
Hi,
You could use these three formulas with 2000 in A1 and these in the next three cells: =INT(A1/36) =INT(A1/6-A2*6) =A1-36*A2-6*A3 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kinsey" wrote: Hello all, I have 2000 items and want to represent them as full cartons - inner cartons and single items. Full cartons have 36 pieces inner cartons have 6 pieces and singles are self explanatory. I tried using the INT formula but I can't seem to get it to work. Is there something else I can use? So want it to look like this stock 2000 - master cartons 55 ( 55x36=1980) inner cartons 3 ( 3 x 6=18) singles 2 ( 2 x 1) regards -- kinsey |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
stock control question
Sean/Shane, thanks for your solutions. The first two work fine but the last
one gave me a negative figure of -18 when it should be 2. Let me explain total items - 2102 (i'm using a different product here) first box item 26 master cartons x 80 pieces = 2080 (solution shows 26. Great!) second box 2 inner cartons x 20 pieces = 40 (solution shows 2. Good!) 3rd box is just singles left and should be 2 but gives me -18 Can you help ? Regards Peter -- kinsey "Sean Timmons" wrote: Assuming 2000 is in A1, all below formulas will go A2 - A4 =INT(A1/36) =INT(MOD(A1,36)/6) =A1-(A2*36+A3*6) "kinsey" wrote: Hello all, I have 2000 items and want to represent them as full cartons - inner cartons and single items. Full cartons have 36 pieces inner cartons have 6 pieces and singles are self explanatory. I tried using the INT formula but I can't seem to get it to work. Is there something else I can use? So want it to look like this stock 2000 - master cartons 55 ( 55x36=1980) inner cartons 3 ( 3 x 6=18) singles 2 ( 2 x 1) regards -- kinsey |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
stock control question
If you change your number per carton, y ou have to change the formulas
accordingly =INT(A1/80) <- 80 is your first grouping =INT(MOD(A1,80)/20) <-80 is your first grouping, 20 is your 2nd grouping =A1-(A2*80+A3*20) <-Again 80 for your first, 20 for your second. "kinsey" wrote: Sean/Shane, thanks for your solutions. The first two work fine but the last one gave me a negative figure of -18 when it should be 2. Let me explain total items - 2102 (i'm using a different product here) first box item 26 master cartons x 80 pieces = 2080 (solution shows 26. Great!) second box 2 inner cartons x 20 pieces = 40 (solution shows 2. Good!) 3rd box is just singles left and should be 2 but gives me -18 Can you help ? Regards Peter -- kinsey "Sean Timmons" wrote: Assuming 2000 is in A1, all below formulas will go A2 - A4 =INT(A1/36) =INT(MOD(A1,36)/6) =A1-(A2*36+A3*6) "kinsey" wrote: Hello all, I have 2000 items and want to represent them as full cartons - inner cartons and single items. Full cartons have 36 pieces inner cartons have 6 pieces and singles are self explanatory. I tried using the INT formula but I can't seem to get it to work. Is there something else I can use? So want it to look like this stock 2000 - master cartons 55 ( 55x36=1980) inner cartons 3 ( 3 x 6=18) singles 2 ( 2 x 1) regards -- kinsey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stock control help | Excel Discussion (Misc queries) | |||
stock control | Excel Discussion (Misc queries) | |||
STOCK CONTROL IDEAS | Excel Discussion (Misc queries) | |||
stock control system | Charts and Charting in Excel | |||
Stock control..help please | Excel Discussion (Misc queries) |