# How many Kits Available

November 17, 2017 - by Bill Jelen

Today, an interesting Excel problem about bills of material. You have a lot of raw materials. Each item might be assembled into several different top-level assemblies. Based on the raw material on hand, do you have enough to fulfill an order for a certain item?

## Watch Video

- Tim asks: How many of each item is available to sell
- Complicating factor: An item is comprised of multiple cartons
- Bill Method #1: Add a helper column with INT(Qty Needed/On Hand)
- Add Subtotals for the Min of Helper at each change in Product
- Collapse Subtotals to the #2 View
- Select all data. Use
`Alt + ;`for Select Visible Cells - Paste to a new range
`Ctrl + H`to change Space Min to nothing- Mike Method #2
- Copy the Product column to the right and use Data, Remove Duplicates
- Next to the unique list of products, use MINIFS
- Note that MINIFS is only available in Office 365
- Bill Method #3: a regular pivot table fails because Calculated Fields won't work in this case.
- Select one cell in your data and press
`Ctrl + T`to convert to a table. - Instead, as you are creating the pivot table, choose the box for Add to Data Model
- Create a new measure for Available to Sell using INT
- Create a new measure for Kit Available to Sell using MINX
- That pivot table works!
- Mike Method #4 Use AGGREGATE function.
- It seems like you would want to use MIN argument, but use SMALL because it handles arrays
- Use
`=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1)`

- AGGREGATE is one of five functions that can accept an array as an argument without
`Ctrl + Shift + Enter` - Bill Method #5
- Convert the data to a table and use Power Query - aka Get & Transform
- In Power Query, calculate OH/Needed
- Use the Number.RoundDown function to convert to integer
- Use Grouping by Part Number and Min Avail
- Close & Load
- Bonus: It is refreshable!

### Auto-Generated Transcript

- hey welcome back it's time for another
- dueling Excel podcast I'm Bill Jelen
- from MrExcel be joined by Mike Kerwin
- from excels phone this is our episode
- 190 how many kits are available to sell
- all right today's question Assen and by
- Tim watches our dueling excel videos he
- works for a retailer and asked to create
- a spreadsheet it to show our sales team
- what we own and what we can sell sounds
- simple right but here's the catch the
- item they sell contains multiple cartons
- and are inventoried on a per carton
- basis here's an example what he's saying
- seeing so here's this item P one two
- three four five has three different
- things that they have to ship and in the
- kit requires four of Cardon one one of
- card in two in one of carton three and
- this is how many they have in stock
- alright so just doing the math here they
- have two complete sets of carton one for
- complete sets of carton two and three
- complete sets of carton three but that
- means the what they can sell is the
- minimum of those three numbers like on
- my cell - and here they have four
- complete sets of carton for four of
- carton five two of carton three only one
- of carton seven that's the limiting item
- so in this case they can only sell one
- of these all right now a question for a
- later day I said well is there any
- chance that carton 3 is used in more
- than one place and he says yes but we're
- gonna worry about that later
- alright so here's how I'm gonna attack
- this I can actually think of several
- different ways to attack this so this
- might be interesting this might be a
- back and forth type of duel what I'm
- gonna do is I want to have a helper
- column out here and the helper column is
- gonna look on an item-by-item basis of
- how many we can sell so equal eight
- divided by four like that and we'll
- double click - to copy it down but let's
- say that we needed four and we had six
- alright so now it's gonna say 1.5 well
- you can't sell you know half a couch
- alright so it's going to have to be the
- whole number so what I'm gonna do here
- is use the int int the integer that
- thing which will take off the decimals
- and leave us just
- all right so then we have eight back to
- the original number and we need to
- figure out for each item here what's the
- smallest number over and column e make
- sure the data sorted by product go to
- the data tab choose subtotals at each
- change in product use the min function
- you know I teach subtotals all the time
- of my power Excel seminars and I point
- out there's 11 functions here but I've
- never used anything other than sum and
- count
- so while subtotal may not be the fastest
- way to do this I want to be able to say
- that there was actually one time where I
- was able to use something other than sum
- and count all right click OK and we're
- gonna get is every time the curtain
- number the product number changes we get
- to see them in and that min is the
- answer that we want so I collapsed down
- to the number two of you I'll select all
- of this data and alt semicolon to select
- just the visible cells ctrl C and then
- we'll come down here and paste let's
- just paste out to this area control V
- alright delete the extra columns and
- then we have to get rid of the word min
- and not just the word min but space min
- alright so I'm gonna use control H and
- change the recurrence of space min to
- nothing my place all click OK click
- close and there's our table what we have
- available to sell all right Mike throw
- it over to you Wow
- MrExcel I love it the min function in
- subtotals how cool is that
- alright I'm gonna go over to this sheet
- right here I'm gonna do the same helper
- column int will take on hand / rec
- required quantity close parentheses
- control enter double click and send it
- down now I just need to find the men
- available for a given condition or
- criteria I'm gonna select product
- control shift down arrow control C to
- copy then I'm gonna right arrow control
- V then I'm gonna come up and say remove
- duplicates there it is I used to use
- advanced filter unique wreck
- it's only all the time but it seems like
- this method is faster
- there's my unique list now I'm gonna
- come over here how many and I'm gonna
- use the new function man yeah snowmen
- ifs is in office 365 for Excel 2016 or
- later the min range well I need to find
- the minimum value in this column control
- shift down L f4 comma and the criteria
- range that's gonna be this whole product
- control shift down arrow f4 comma left
- arrow and there we go that will get the
- min value from how many based on the
- condition or criteria close parentheses
- control enter double click and send it
- down all right so there's min ifs and
- subtotal I'm gonna throw it back over to
- you MrExcel yes Mike very nice remove
- duplicates kdeeny list of products and
- then the min ifs function
- I asked him what version of Excel he's
- on he said Excel 2016 I hope it's office
- 365 version of 2016 so he has access to
- that well how about a pivot table all
- right so I created a pivot table with
- product and requires some are required
- quantities some of them on hand then
- from right here analyze fields items and
- sets calculated field and created a new
- calculated field called available which
- is on hand / required quantity that way
- I don't need the help or column over
- here and it first it seemed like it was
- gonna work because we had two three and
- four and the reporting that the minimum
- is two I change this calculation of
- course to min and that seemed good but
- then on this one where we have two four
- four one two it's reporting three and
- what's happening is it's doing the
- calculation on this row we have 25 on
- hand divided by eight that's three in a
- fraction and so it's reporting three and
- so no a regular pivot table calculate
- item is not gonna work but instead
- convert this data to a table and an
- insert a pivot table add this data to
- the data model click OK
- and we're gonna have down the left-hand
- side product and what it requires I'm
- gonna create two implicit measures here
- with a required quantity and some of on
- hand and then I'm gonna create a new
- measure so PowerPivot measure a new
- measure and this new measure will be
- called available to sell and that
- formula is going to be how many we have
- on hand divided by how many are required
- for each item and click OK
- alright so 8 divided by 4 is 2 all right
- now that's still not our right answer
- and we probably need to run this through
- the the integer function so measures
- where I manage our measures edit this
- and wrap the whole thing inside the int
- function like this click OK and click
- close now we're getting a fractional
- number still the wrong answer here but
- we're gonna use a great new function
- that's only available in Dax new measure
- and this is gonna be called kit
- available and the function is not min
- but min X min X the Minx function and
- the table that we use is Table one and
- then expression is going to be that
- available to sell that we just
- calculated and what this does the min X
- function evaluates on a row by row basis
- and finds the minimum error and so will
- click kit available okay I'll check this
- out so here where we have two four four
- one and two it's reporting one all right
- now in a perfect world all we have is
- product and kit available we don't need
- any of this other stuff in the middle
- all right so we're just gonna check this
- here 2 1 3 2 are our answers I'll take
- the requires out 2 1 3 2 yes it's gonna
- work we actually take all the
- intermediate calculations out
- just have a kit available like that Mike
- do you have another one how cool is that
- MrExcel you use the Minx function in
- Dax well I'm gonna go back over here I'm
- gonna use a formula but I'm gonna
- pretend like I don't even have this help
- or column
- I used min ifs well before men ifs in
- Excel 2016 there was the aggregate
- function in Excel 2010 now I want to use
- man but of course functions 1 to 13 do
- not let you do array formulas so I'm
- gonna have to use small one as a
- substitute for the min function and
- small is one of the functions 14 and
- above that can handle array operations
- that argument right there array so
- function number 15 comma I want to
- ignore divided by zero error so I'm
- going to type a 6 to ignore errors comma
- and I need to simulate that whole helper
- column in the array argument I am T and
- instead of simply saying on hand divided
- by require we do the whole column
- control shift down arrow f4 divided by
- the required column control shift down L
- f4 now close parenthesis that int right
- there if I highlight this and hit f9 it
- simulates that entire how many helper
- column control Z now I simply divided by
- in parentheses I need to get an array of
- trues and falses so I click on product
- control shift down f4 and I ask the
- question are any of you equal to that
- product ID close parentheses that will
- give me a bunch of trues and falses f9
- trues and falses in the denominator true
- will become a 1 false will become a 0
- which will give us divide by zero error
- control Z in essence if I click the
- whole array in here
- f9 the divide by zero is going to be our
- filter so we only see the numbers for a
- particular product control Z and then of
- course aggregate will pick the min out
- from that array of errors and numbers
- close parenthesis and aggregates amazing
- one of five functions that has an
- argument that can handle array
- operations without control shift enter
- so I simply control enter and f2 what
- did I forget
- backspace array then I type a comma and
- the K is 1 because I always want small
- one which is the min close parentheses
- control enter double click and send it
- down f2 all right aggregate with that
- whole helper column right there to get
- how many for each product all right I'm
- gonna throw back over to MrExcel hey
- that's beautiful I knew there'd be a lot
- of different ways to solve this I did
- not think of using aggregate which of
- course is better because if someone has
- 2010 this will work the 15 allows an
- array out here that is gorgeous
- all right now hey when I set up the
- question I just missed this and you know
- and and Mike you know this when people
- send us questions they try and minimize
- the situation to make it sound like it's
- easy but the thing that's gonna be a
- disaster here is the fact that carton 3
- is used in multiple places alright and
- as soon as they sell something from
- let's say they sell like this item P 1 2
- 3 4 6 well then the number of carton
- threes on hand it's gonna change right
- and so that's gonna impossibly impact
- what else we can sell alright so
- thinking about how Tim is gonna have to
- manage this process he's gonna have to
- wait I have a way to regenerate this
- this item quickly and so hopefully he
- has an inventory table for every item
- it'll show how many there are on hand
- and then a vlookup here to pull the
- inventory over alright that's what I'm
- hoping is going to happen because then
- it might become somewhat manageable and
- if this is something we have to
- reproduce again and again and again
- then power query definitely has a use
- here so power query in Excel 2010 or
- 2013 you're gonna go download it you'll
- have your own power query tab but in
- Excel 2016 you're gonna look for the get
- and transform it's funny in Excel 2016
- it was the second group but then in
- office 365 they moved it to be the first
- group the power query has the ability to
- take something from a table or range so
- I'm gonna choose one cell in this table
- ctrl T that will create a table for me
- table 3 is a fine name I don't need to
- rename that now this is a table we go to
- data from table or range and we are
- going to add a new column this column is
- gonna be a custom column it's gonna be
- called available and that is going to be
- the on hand divided by required quantity
- all right now we need to send this into
- the I and T function and for slaying the
- functions and power query are not the
- same so click here and then go to
- formula types and you'll find this
- function is called number dot round down
- and this is case sensetive you have to
- make sure to use that exact same case so
- equal number dot round down open paren
- and closed paren and we click ok and so
- 11 divided by 4 is two point seven five
- rounds down to two alright that's the
- answer we need there we don't need these
- columns anymore site click on requires
- shift click on on hand and remove those
- columns all right now choose product
- transform group by or a group by the
- product and the new function is going to
- be called kits available and the
- operation is going to be the min of the
- available column click OK alright so now
- we have product and kits available home
- close and load get a brand new sheet
- with our answers but here's the
- beautiful thing all right so when we
- sell something let's make these columns
- less wide and we sell let's say we sell
- enough so we have no curtain threes left
- I change that number there
- that vlookups bring the results and then
- come back here and choose this and
- refresh all and you see that now we have
- none of this and this and this available
- to sell because they all needed that
- garden 3 and we have none of those left
- being able to refresh that by our query
- is going to help this in the end well
- this was a fun one
- for me because I knew there would be a
- lot of different ways to solve this
- problem the the episode wrap up of this
- really long episode how many of each
- item is available sell and there's
- multiple cartons alright so the first
- thing I did was add a helper column and
- then use subtotals with the min function
- and then a whole bunch of really boring
- steps micro method number two used min
- ifs which is great if you have office
- 365 I went back to a pivot table but a
- regular pivot table won't work instead
- have to do a data model and then use the
- Minx function the min x function and
- that calculated field or measure will
- actually work Mike using the aggregate
- function beautiful function one of five
- functions that can accept an array as an
- argument without control shift enter and
- then method 5 convert the data to a
- table and use power query also known as
- get and transform and we're gonna
- calculate on hand divided by needed and
- then the number dot round down function
- to convert to an integer group by part
- name number and calculate the minimum
- available close and load in the bonus
- it's refreshable well hey i want to
- thank you for stopping by we'll see you
- next time for another dueling excel
- podcast from MrExcel and excel is fun

Download File

Download the sample file here: Duel190.xlsx

*Title Photo: minarikovamichaela87 / Pixabay*