Excel - Dueling Excel - How many Kits Are Available From This Inventory - Duel 190 - Episode 2177

2,563 views · Published 17 November 2017 · 17:07 · Indexed 16 November 2026

Channel: MrExcel.com · 2017 · Education

Watch on YouTube

Microsoft Excel Tutorial: How many Kits Can We Make From This Inventory?

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!

#excel
#microsoft
#microsoftexcel
#exceltricks
#microsoft365
#excelduel
#excelchallenge
#excelformula
#excelformulasandfunctions
#evergreen
#powerquery

Table of Contents
(00:00) Excel Challenge: How Many Kits Are Available to Sell?
(00:37) Kit has a bill of material
(01:32) Bill with Helper Column
(01:58) Remove Decimals with INT function
(02:27) Subtotals in Excel with the MIN function!
(03:18) Replace All Min
(03:51) Mike Heler Column
(04:11) Remove Duplicates
(04:32) MINIFS in Excel
(05:33) Bill with Pivot Table with a Calculated Field
(06:25) Pivot Table based on Data Model with Calculation
(07:52) Using MINX function in DAX
(09:10) Mike uses AGGREGATE function with SMALL
(12:44) Scaling for Real Life Data
(13:16) Getting Power Query
(14:07) INT in Power Query is Number.RoundDown
(15:44) Refresh in Power Query is a game-changer
(16:03) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Comparing different methods to calculate available kits for sale in Excel
Creating a helper column in Excel
Creating a Pivot Table in Excel for inventory tracking
Excel sales team inventory tracking
How to calculate available kits for sale in Excel
Using DAX functions in PowerPivot for Excel inventory analysis
Using the AGGREGATE function in Excel 2010
Using the INT function in Excel
Using the MIN function in Excel
Using the MINIFS function in Excel 2016
Using the SMALL function as a substitute for MIN in Excel
YouTube video on Excel sales spreadsheet


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154002/

More from this channel