Excel division

%3CLINGO-SUB%20id%3D%22lingo-sub-1864226%22%20slang%3D%22en-US%22%3EExcel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864226%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20assisted%20with%20a%20formula%20that%20can%20calculate%20as%20per%20below%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B1.%20Check%20the%20Quantity%20ordered%20for%20a%20given%20variety%20and%20then%20divide%20this%20amount%20by%20the%20maximum%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bpack%20rate.%20to%20determine%20the%20number%20of%20boxes%20to%20be%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%202.%20Arrange%20this%20information%20as%20per%20the%20example%20in%20column%20in%20Column%20H%20and%20I.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1864226%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864304%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864304%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40AK_ABDI%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20solution%20with%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864306%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864306%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Detlef%2C%20Would%20you%20mind%20to%20explain%20how%20you%20worked%20it%20out%20with%20power%20query%3F%20Not%20familiar%20with%20power%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864325%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40AK_ABDI%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20view%20the%20query%20right%20click%20on%20the%20green%20table%3A%20Table...%20-%26gt%3B%20Edit%20query.%3C%2FP%3E%3CP%3EOn%20the%20right%20is%20a%20pane%20with%20the%20individual%20steps.%20Some%20have%20a%20gear%20icon%20which%20gives%20more%20detailed%20information%20then%20the%20formula%20bar.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864334%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864334%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40AK_ABDI%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%20in%20attached%20file%2C%20you%20can%20find%20a%20formulas%20based%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22amit_bhola_0-1604778291698.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232161i46FD9A0A528642D4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22amit_bhola_0-1604778291698.png%22%20alt%3D%22amit_bhola_0-1604778291698.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864403%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20play%20with%20M-script%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Tabelle1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20addList%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22Pack%20rate%20%2F%20box%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20v%3D%20%5BQty%20ordered%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20n%20%3D%20%5BMax.%20Pack%20rate%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lst%20%3D%20List.Numbers(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5BMax.%20Pack%20rate%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5BQty%20ordered%5D%2F%5BMax.%20Pack%20rate%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5BMax.%20Pack%20rate%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20trs%20%3D%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lst%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%20if%20_%20%26gt%3B%20v%20then%20v-_%2Bn%20else%20n%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%5Btrs%5D%2C%0A%20%20%20%20%20%20%20%20Int64.Type%0A%20%20%20%20)%2C%0A%20%20%20%20expandList%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20addList%2C%0A%20%20%20%20%20%20%20%20%22Pack%20rate%20%2F%20box%22%0A%20%20%20%20)%2C%0A%20%20%20%20addIndex%20%3D%20Table.AddIndexColumn(%0A%20%20%20%20%20%20%20%20expandList%2C%0A%20%20%20%20%20%20%20%20%22Box%20%20Nr.%22%2C%201%2C%201%2C%20Int64.Type%0A%20%20%20%20)%2C%0A%20%20%20%20keepReportColumns%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20addIndex%2C%0A%20%20%20%20%20%20%20%20%7B%22Varieties%22%2C%20%22Pack%20rate%20%2F%20box%22%2C%20%22Box%20%20Nr.%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20keepReportColumns%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865149%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3B%20guys%20!%3C%2FP%3E%3CP%3EThough%20I%20need%20to%20learn%20power%20query.%20Posting%20this%20question%20opened%20up%20a%20new%20chapter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865155%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865155%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40AK_ABDI%3C%2FA%3E%26nbsp%3B%2C%20till%20that%20time%2C%20did%20you%20note%20the%20formulas%20based%20solution%20i%20posted%3F%20It%20is%20not%20as%20elegant%20as%20Power%20Query%2C%20but%20could%20help%20you%20for%20the%20time%20being%2C%20perhaps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865160%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formulae%20is%20noted%20though%20power%20query%20as%20demonstrated%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20is%20really%20powerful.%20I%20just%20add%20more%20varieties%20and%20hit%20the%20refresh%20button%20and%20the%20magic%20happens%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EI%20have%20just%20become%20more%20curious%20by%20checking%20videos%20on%20Trump%20excel%2C%20excel%20campus.%20If%20any%20of%20you%20has%20some%20simpler%20materials%2C%20please%20share.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%20appreciated%20and%20I%20keep%20both%20solutions%20for%20my%20records.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865163%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20division%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865163%22%20slang%3D%22en-US%22%3ECertainly%2C%20Power%20Query%20is%20a%20whole%20new%20dimension%20to%20Excel%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi,

 

I would like to be assisted with a formula that can calculate as per below;

   1. Check the Quantity ordered for a given variety and then divide this amount by the maximum           pack rate. to determine the number of boxes to be used.

    2. Arrange this information as per the example in column in Column H and I.

 

Thanks in advance.

 

10 Replies
Highlighted

@A_SIRAT 

Here is a solution with Power Query.

 

Highlighted

Thanks Detlef, Would you mind to explain how you worked it out with power query? Not familiar with power query.

 

Highlighted

@A_SIRAT 

To view the query right click on the green table: Table... -> Edit query.

On the right is a pane with the individual steps. Some have a gear icon which gives more detailed information then the formula bar.

Highlighted

@A_SIRAT ,  in attached file, you can find a formulas based solution.

 

amit_bhola_0-1604778291698.png

 

Highlighted

@Detlef Lewin 

To play with M-script

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    addList = Table.AddColumn(
        Source,
        "Pack rate / box",
        each
            [
                v= [Qty ordered],
                n = [Max. Pack rate],
                lst = List.Numbers(
                    [Max. Pack rate],
                    [Qty ordered]/[Max. Pack rate],
                    [Max. Pack rate]
                ),
                trs = List.Transform(
                    lst,
                    each if _ > v then v-_+n else n
                ) 
            ][trs],
        Int64.Type
    ),
    expandList = Table.ExpandListColumn(
        addList,
        "Pack rate / box"
    ),
    addIndex = Table.AddIndexColumn(
        expandList,
        "Box  Nr.", 1, 1, Int64.Type
    ),
    keepReportColumns = Table.SelectColumns(
        addIndex,
        {"Varieties", "Pack rate / box", "Box  Nr."}
    )
in
    keepReportColumns
Highlighted

@Detlef Lewin

@Sergei Baklan 

 

Thank you  guys !

Though I need to learn power query. Posting this question opened up a new chapter.

 

Highlighted

@A_SIRAT , till that time, did you note the formulas based solution i posted? It is not as elegant as Power Query, but could help you for the time being, perhaps!

Highlighted

@amit_bhola 

 

The formulae is noted though power query as demonstrated by @Detlef Lewin  is really powerful. I just add more varieties and hit the refresh button and the magic happens 

I have just become more curious by checking videos on Trump excel, excel campus. If any of you has some simpler materials, please share.

 

Anyway appreciated and I keep both solutions for my records.

Highlighted
Certainly, Power Query is a whole new dimension to Excel
Highlighted

@Detlef Lewin 

 

Hi,

 

I have rephrased the excel question in a different way..." throwing a spanner in the works".

The maximum that can be packed in a box in 1500. After a box is filled up, then the division continues downwards i.e. if the left over is 200 for Jenny for box nr 3, then this will be filled up by 1300 sylvia which is the next variety.

I have attached an example file.

 

Please assist and thank you for your efforts.