 # 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 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.

10 Replies

# Re: Excel division

Here is a solution with Power Query.

# Re: Excel division

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

# Re: Excel division

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.

# Re: Excel division

@A_SIRAT ,  in attached file, you can find a formulas based solution. # Re: Excel division

To play with M-script

``````let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
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(
"Pack rate / box"
),
expandList,
"Box  Nr.", 1, 1, Int64.Type
),
keepReportColumns = Table.SelectColumns(
{"Varieties", "Pack rate / box", "Box  Nr."}
)
in
keepReportColumns``````

# Re: Excel division

@Detlef Lewin

Thank you  guys !

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

# Re: Excel division

@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!

# Re: Excel division

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.

# Re: Excel division

Certainly, Power Query is a whole new dimension to Excel

# Re: Excel division

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.