Forum Discussion

A_SIRAT's avatar
A_SIRAT
Iron Contributor
Nov 07, 2020

Excel division

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 rat...
excel
A_SIRAT's avatar
A_SIRAT
Iron Contributor
Nov 07, 2020

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

 

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Nov 07, 2020

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.

  • A_SIRAT's avatar
    A_SIRAT
    Iron Contributor
    Nov 08, 2020

    Detlef_Lewin

    SergeiBaklan 

     

    Thank you  guys !

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

     

    • amit_bhola's avatar
      amit_bhola
      Iron Contributor
      Nov 08, 2020

      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!

      • A_SIRAT's avatar
        A_SIRAT
        Iron Contributor
        Nov 08, 2020

        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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 07, 2020

    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

Resources