Excel division

Question

Hi,&nbsp;I would like to be assisted with a formula that can calculate as per below;&nbsp; &nbsp;1. Check the Quantity ordered for a given variety and then divide this amount by the maximum&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pack rate. to determine the number of boxes to be used.&nbsp; &nbsp; 2. Arrange this information as per the example in column in Column H and I.&nbsp;Thanks in advance.&nbsp;

detlef_lewin · Answer

A_SIRAT&nbsp;Here is a solution with Power Query.&nbsp;

sergeibaklan · Answer

Detlef_Lewin&nbsp;
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 _ &gt; 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

a_sirat · Answer

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

detlef_lewin · Answer

A_SIRAT&nbsp;To view the query right click on the green table: Table... -&gt; 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.

amit_bhola · Answer

A_SIRAT&nbsp;,&nbsp; in attached file, you can find a formulas based solution.&nbsp;&nbsp;

Forum Discussion

Excel division

10 Replies

Resources