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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920