I'm a retired aerospace engineer. Reading the comments I am pleasantly surprised at how many engineers find your video valuable. As reliability engineers we never dealt with bill of material concerns. Our concerns were how long the parts of a jet engine would last. We needed to know when they were likely to fail and develop a replacement strategy before even early failures might crop up. Typically Weibull analysis is used for that. Back in the late 80s we paid $35K to analyze each part. When Excel came along, I built a spreadsheet to solve the Weibull equations and thus saving us boatloads of money. Later in the 90s others created dedicated Weibull software to do the work, but for small shops with few parts to study, Excel is fine. Thanks for branching out of your comfort zone. There is a lot more in Excel to explore.
(Background is a Mechanical Engineer) The Rept function seems really handy but it contaminates the dataset with spaces making left or mid functions a pain, so instead I use conditional formatting for it so its only a visual indent rather than editing the raw data. So an example would be in your data, I conditional format on column B with a range of B:B, I then use a formula of A1=1 , Id then repeat it how ever many tiers there are in the bom so at bom level 5 would be A1=5 The formatting would then be under the number tab, use custom and then I use without quotations " @" This lets me add how ever many spaces I want each time and if I ever export or do formulas on the data it wont have any new characters added A big benefit for this is that it auto indents in an entry field so if you put in results it then it will format the moment you leave the cell, currently I use this with Tier numbers for an excel based gantt chart I built =) But I will likely pinch the rept and trim combo for other things as this can come in handy. The other thing I like to do on nested functions is to write it as if it was like code so use "alt" & "enter" at the same time in the formula screen lets you step the formula, when you have huge nested functions then its an absolute godsend if there's something needing changing 3 months from now.
(Background is in aeronautical writing/aircrew operations) I have to agree about using conditional formatting instead of using REPT (or inserting hard spaces in the raw data), and using the key combination Alt+Enter for a line break in a formula. REPT is a holdover from the early Lotus 1-2-3 spreadsheet program's @REPEAT function (and possibly VisiCalc) which didn't organically have a way of adding top/bottom borders to cells. @REPEAT was used in the row below data to "underline" (with whatever character combination) that data. 1-2-3 also had a clunky way of inserting page breaks, too -- but I digress.
So cool Leila, just last night helped my friend who is engineer for something similar. And she said she needs to learn excel 😃. I shared your video now. What a coincidence! Thank you 🙌
It would be nice to see a video on Trigonometric functions engineers use a lot. You can use the elapsed hours function to demonstrate degrees, minutes and seconds ([H]:MM:SS). Also if multiplied by 24 and format numbers you get degrees and decimals thereof (DD.DDDD). All trigonometric functions in Excel require or return radians, hence you can use the built-in functions (RADIANS and DEGREES) to get desired results or input. Thanks for the video content, awesome presentation and voice!
I'll also watch a detail about why that function worked. I've tried similar functions and found issues. Does it work if an item is repeated in two locations in a BOM? Example: a left and right wheel assembly each have a tire, wheel, and valve stems - but each assembly are under different sub assmeblies. Not an ideal example but it's not uncommon to have the same items in multiple locations in a BOM.
@@miamisasquatch My approach would be to include a Handed column and incorporate it into the formula. Either that, or I wonder if Unique could be used? Or the way the data preparation table is setup in Leila's video on the dynamic stepped line graph?
Haven't used or needed the majority of these in my engineering career to be honest...apart from IF statements. Two things that I do use a lot are Xlookup and LINEST - both are life changing!
Sadly, if the item levels reach into more than one digit, the substitute function at 5:00 can blow up. 1.11.2.1 would become 11121 and be counted as level four where it is actually three. Better to count the instances of decimal points.
At that point it would be best to use power query to split columns by delimiter 1st. You can also you excel text to columns but then you couldn’t refresh it
Along the lines of the LEFT and RIGHT functions, I also find useful to join data together with the CONCATENATE function. This has been useful for situations where Excel creators are making forms for the less technical users. One example would be that you can make output sentences in plain English wrapped around your changing variables.
Thank you for realizing that non fanatical professionals use Excel too!!! Although I'm not an engineer, I do work in industry and find many uses for Excel when working with large data sets and analysis, many adapted from your channel. Thank you for your in-depth explanations and tutorials. Keep up the good work and keep looking outside of the box.
I like the example of LOOKUP, and the clever use of 1/(array of true/false) to find the position of the only value that's true and then pull the matching result. An in-depth of these type of array combinations may come in handy for many.
You explain so nice, you are a wonderful person. People watch your videos not only because of the Excel knowledge, but because of you and the fact that you are a very nice and kind person.
Hi Leila, I am a Planning Engineer from Mechanical background. I often extract data from Primavera to excel to prepare progress reports and it use to take alot of man hours wasted everytime in arrange each activity and distribute weightage. This video helped me to complete my tast in short duration and now i can generate reports in no time. Thankyou, keep posting such videos. These videos are much useful in day-to-day desk work to optimise manhours in compliting tasks.
Im a finance guy and I use the substitute function to remove spaces from my data sets (mainly stock count figures) so it's exports nicely into our accounts system. ... You have shown me the trim function and it's so much easier!! Thank you.
Really nice trick for the lookup formula, thanks! I'll keep it in mind! If I understand correctly, it creates a vector of booleans, then searches for the last occurrence above the lookup value that matches BOM level -1, for anyone wondering
Great video! However, one vital function that you left out on and I have found super useful for engineering applications is CONVERT! Great for everyone struggling to remember conversion from imperial to SI (normal) units!
As an engineer you shouldn't use imperial at all. Most famous fail for using imperial units led to a failed satellite project, didn't it? Just get used to SI so you won't need that kind of conversions at all eliminating a totally unneccessary source of mistakes. And isn't exactly that the core and center of engineering?
I do prefer and have my base in SI but living in US where the contracts demands US customary units, you simply need to use this system. When in Rome....
I made a great macro for finding extended quantites in a BOM with item number. It steps down line by line, checks for repeats, updates an array with quantity at the array element corresponding to bom level (by counting decimals in the string), change all elements to the right to a one, and finally multiply all array elements for the total quantity.
Leila, you teased us with that Lookup magic, now, please, please do a dedicated video on it. Thank you for the great content you provide us with! Happy holidays!
As a Mech Eng, I can confirm these are definitely useful tips and this is also how I set up my BOMs 👍 Haven't really seen anyone else mention it, but you could use this to fully automate how many parts you would need to order for a specific items. For eg, I've set up one sheet with all the possible assembly parts (Level 0) next sheet is the BOM, and the following sheet is all the individual part items. I use a VLOOKUP to count how many assemblies I need in the BOM sheet based on input from the first sheet. Then the last sheet I would use COUNTIF to sum the parts I'd need to order at the end of the day. Very robust way in making sure you order enough parts to complete the assemblies you need!
I'm an electrical engineer, instead of "right" function, I used to use "ext.text" with the first character defined by the "find" (plus 1) function and for number of characters I put a number that surely cover all instances, like 100 characters, excel will extract only the characters on text, will not leave the characters with dozens of spaces. btw good channel I'm learning a lot
This is so helpful. Also not an engineer but as an accountant we are often required to show expenses by cost centers that are structured in a hierarchical manner. I see opportunities in using this tutorial in pulling out correct level of the hierarchy. Thank you I shared with my team.
Thanks for the video content. I liked the Calculating Extended qty. For me, I can use it for the Bill of Quantity (BOQ) to find the project value in construction.
Funny thing, i was a mechanical engineer, and now i'm an Excel consultant :-) And still learning new things. Thanks for the video, now i now what the rept function can be used for!
thank you Leila! please could you do an extended video on the lookup function and combining of various functions together! thank you so much, i learn so much from you from every video you make 😊
i have a topic suggestion for engineers/technicians 🤔 (maybe) i did a few days ago a diagram with values over time. When calculating the hours or comparing them with the IF Function, i had massive problems, because the excel time-object (hours:minutes:seconds) has on the 10th place after the decimal point some inaccurancies when represented as a decimel number... and it has in itself somehow the day in it. when i wanted to compare for example 00:10 with 00:10 i couldnt get a TRUE back, because wether the decimel number which this time represented has some differences in the 10th place after the decimal point (f.e. 0.166666678 and 0.166666698), so i had to use the ROUND function around every value ... or the time was at another day, so 00:10 can be 0.16666667 for today, 1.16666667 for tomorrow, 2.16666667 for in 2 days...and so on. when i wanted to make an addition of two time values i had to make something like this: 00:10 + 1 for adding 1 day 00:10 + 1/24 for adding 1 hour 00:10 + 1/(60×24) for adding 1 minute 00:10 + 1/(60×60×24) for adding 1 second this was really hard work to find out...especially because i had a deadline for this... maybe this helps somebody cya😊👍
You just helped me realize I've been doing IFERROR functions wrong this whole time. I've been using them as the logical test part of IF statements and not knowing they could work on their own. This will have a big impact on the size of many of my formulas! Thank you.
Another great function for BOMs is a sumpdoduct to generate total costs. It gets tricky if you have multiple of the same item in the BOM and so you need a "tree/path" column. My preferred format is old AS400: 'Parent::Childlvl1::Childlvl2::Childlvl3::Childlvl4' etc. Using this you can sumproduct by the path then multiply by the quantities to get accurate cost roll ups generated (I spread it out over a few columns to prevent nesting and show the cost contributions.
Really very useful functions for me , All the time I need to mess up with huge files having Multilevel BOM of thousands of components. Thanks for making my life easier.
Thanks for the tips! As engineer, I would wish to see in Excel in future, some function that converts a formula in a cell in a readable formula. I mean, in engineering calculations there some complex equations which is difficult to audit or evaluate it when you saw in Excel formula fashion.
There’s a button that will convert the cells values back to the formula used to calculate them. In the Formulas tab, simply click Show Formulas in the Formula Auditing group to view the formulas. Click the button again to return the values.
Hi Leila, very useful video. However, the example would seem to be more applicable to someone from stores/purchasing than the engineer. My last engineering workbook consisted of lots of calculations (calculating bearings between GPS locations - including lots of conversions, extracting data for certain bearings and drawing graphs for those data). Maybe you could do a video on some actual engineering functions.
It'd be great to see these functions as well. But I know our design engineers, quality management and production routing engineers were using functions like these for a good many years until we adopted a PLM system.
I'm an engineer in a structural analysis group, and I use every function she included except "rept" and technically "lookup" since I usually use VLOOKUP and occasionally HLOOKUP. 😅
Thanks for the video . I have been using Excel for extracting BOM data. For extracting Part number and Description from BOM ( 6.04 ) , I would use the ( Text to Columns - Delimited or Fixed width ) function to do it becos it only take only few sec !
I work as an industrial engineer and manage the bills of material for my company. While I use most of these on a regular basis, the REPT function for making an indented part number is something I hadn't considered before and will incorporate when exporting BOMs to Excel.
Hi Leila I have been your subscriber for a very long time. And seeing examples that exactly refers to my job is fantastic (I am a mech eng but working as a sakes manager, and mostly dealing with erp reports, bill of materials, extracting and cleaning data reports, etc). Thank you for this great video.
MORE!!!!! Loved the cool Lookup comparing rows...that rocks...I was trying to figure out something like that only a week ago! I use the unique function all the time comparing lists (array) of part numbers, countif=1, and then compare back to the one of the 2 lists to know where it came from! ...then I try to rectify discrepancies for a multitude of reasons!
Hi Leila, a good complement to Left and Right is MID that extract à sub string from a position and with a lenght (or until the end if you Ask 1000), it can be an alternative to your right/len combinaison. Lookup... Yes do à video please.
I personaly use the Table filter buttons(I totaly forgot the name) where you can switch between different parts/locations/schedules just by having buttons on the side of the table, makes quick quantity and if needed, cost checks really easy.
Great list . One common problem from indented boms are cost determinations. Costs are usually listed for each child item and also for the rolled up parent. This effectively doubles the cost of the parent structure. Is there a way to strip out the cost of the parent so you can sum the entire column correctly? BTW I enrolled in your power query sessions. I recommend them to all that read this. It has saved me hours as an engineer tracking project costs.
Hi Leila, I receive files .csv from clients (SAP exports) on a weekly basses to show us their order requirements (we are a manufacture of aerospace parts). One big issue we have is to verify if the client has changed their orders from week to week. They change the delivery dates, add a new requirement or even change the quantities they want and we are measured for on time deliveries based on these. The parts we manufacture have a 4-5 month lead time to deliver. What would you suggest i use to spot these differences and track them to avoid the penalties our client charges us if we are late? Would also be great on how we can track the weekly meetings we have on a line per line item. I am using vlookup but its getting very messy and difficult to track all the meeting notes (following of the production status of each part) on a week to week basis. Thanks again for all you do.
If you are patient enough I work on a program, built completely in VBA for EXCEL, just to address such sneaky, hard to track details. Leave me an email if interested when I launch my product, by August-Sept 2022, I hope. I have seen companies going down, taken advantage by lacking a Business/Projects Tracking Program/Software.
Cool Leila. I”ve really appreciated….please open a section in your videos to show functions in engineering…. It is a huge subject and you can help many many engineers… they will love you much more…..👏👏👏
REPT function i didn't know. All others i know and use sometimes. I have made a calculation sheet for processing of quotations of suppliers. We usually have a negotiated total sum for which i use the goal seek function in the Data menu to calculate the average discount on all items on the list. Also a very handy function.
Thanks! I wasn’t familiar with REPT or LOOKUP. I’m an electrical engineer and I live in Excel much more than my mechanical colleagues in my industry. I have to use Power Query and Power Pivot to pull data out of the horrible Excel files sent to me by other engineers. I also have complex electrical engineering calculations. I need to start using custom functions more often to simplify my formulas
Excellent Stuff. Perfect timing since I'm currently checking engineering procurement because my owner suspects that my colleagues entrusted with purchase have been embezzling from the company.
Great tips! I’m an engineer and usually have to deal with bill of materials. The first functions are the most useful to me (in my case). Thanks once more, Leila!
Hello, I appreciate your tutorials very much but I have a remark for the use case "find BOM level" at 5:58 : this will not work anymore, if a level number exceeds 1 digit. Why not count the delimiter "." instead to determine the BOM-level? BR
Counting the dots is what the formula does. Original length minus the new length when all dots are replaced with nothing "". This formula can be used to count a character in any string.
I like the left and right function, may be they are pretty straightforward, i will try the other functions and see. By the way, can you point me in directions of public data to use for practicing. Let me know, thanking you in advance
Thanks for this - although this is just really mostly text manipulation and that is far from the sole domain of engineers. I work with interpolations and error trapping and radians and complex cross-linked models
