10 Excel Functions You NEED to KNOW as Engineers!
Вставка
- Опубліковано 29 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
This video is a must-watch for all engineers, especially mechanical engineers, who use Excel in their professional lives. It introduces a variety of Excel functions that are critical in engineering work, with a special focus on how these functions can simplify and enhance your workflow. These functions are applicable across all versions of Excel and are tailored to meet the unique demands of engineering tasks.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/excel-engin...
Key Excel Functions Covered:
▪️ REPT Function: Learn how to repeat values effectively for visual hierarchy in data.
▪️ TRIM Function: Discover how to remove unnecessary spaces in data entries.
▪️ LEN & SUBSTITUTE Functions: Master the art of determining hierarchical levels in data.
▪️ LEFT and RIGHT Functions: Gain insights into extracting specific parts of text entries.
▪️ FIND Function: Explore how to locate specific characters within text and manipulate data accordingly.
▪️ LOOKUP Function: Understand the complex yet powerful uses of the original Excel lookup function.
▪️ IFERROR Function: Learn error handling to maintain clean and accurate data.
▪️ IF Function: Dive into logical tests to categorize and analyze data efficiently.
▪️ Bonus - Extended Quantity Calculation: A composite function that combines key elements from other functions for comprehensive data analysis.
All are great for engineers and anyone else who'd like to use Excel more efficiently. If you're working with data for bill of materials, part numbers, or need to calculate extended quantity, this tutorial will help you.
🌍 My Online Excel Courses ► www.xelplus.com/courses/
00:00 The Most Important Excel Functions in Engineering
00:55 Working with a Bill of Material (BOM) in Excel
01:37 REPT Function
03:11 TRIM Function
03:48 LEN & SUBSTITUTE Function
06:04 LEFT
06:42 FIND Function
07:55 RIGHT Function
09:11 LOOKUP Function
12:09 IFERROR Function
12:56 IF Function
13:40 Bonus: Calculating Extended Quantity
14:43 Wrap Up
🎬 LINKS to related videos:
- 5 Excel Functions YOU NEED in 2021: • 5 Excel Functions EVER...
- Basic Excel Formulas and Functions You NEED to KNOW!: • Excel Formulas and Fun...
- Excel IF Function explained in detail: • Excel IF Formula: Simp...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #Billofmaterials #BOM
![Lp. Последняя Реальность #107 РОДНОЙ ДОМ [Финал] • Майнкрафт](http://i.ytimg.com/vi/IK3QKzKUlHM/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/excel-engineers-file
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.
Thanks for the feedback!
(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.
Alt+Enter is the greatest excel tip of all time.
Eng here. Please consider to extend the engineer sessions :) helpful.
Noted! Glad it was helpful.
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 🙌
Hope it will come in handy for her 😊
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!
Thanks for the suggestion, Greg! We'll take a look.
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.
Thank you 👍😊 Leila
1:37 Really interesting use of the REPT function!
10:08 Please do make a video about the LOOKUP function. It’s always confused me a little.
Thanks for the feedback, Andrew!
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?
@@miamisasquatch the 2.1/ ... part in the beginning makes the lookup search upwards. its very weird/poor devwlopment on microsofts side
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
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
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!
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.
Glad it was helpful, Paul!
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.
🙏for every excel tips👌👌🙏🙏from india🙏
Thank you! Very useful and practical. Love how you use the REPT function, very ingenious.
THANK YOU Leila!!
I never miss your videos and will definitely not miss this one as I’m a mechanical engineer myself.
Thank you 🙏
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.
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.
Amazing video, I am always learning something new every time. The part using the LEFT and RIGHT functions combined with FIND was my favorite.
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.
Also the MID function can be added to this beautiful bunch for engineers.
Thanks for sharing.
I'm not an engineer, but the video was very informative, thanks Leila!
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!
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
This is great! I like how you are using the lookup function. I work with very messy data-sets and think this will be useful. Thanks for sharing!
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.
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.
Hi, could you please share that macro?
Thanks Leila, this is so helpful. I work with Bill of Materials a lot and didn’t knew how to do many of the things you explained
Great tutorial. As a novice engineer I have much to learn and apply. Thanks for the excellent video.
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 😊
Thanks Leila, I’m not an engineer, still found these extremely useful.
This is all I ever needed excel for. Working on BOMs will be so much easier. Thank you. More Please
Lots of neat tricks in here. This video is a keeper!
This was great! Love learning new functions in Excel! Thanks!
Thank you so much for your great inputs..!! I loved TRIM function and Lookup Function this will definitely help my day today activity..!!
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 think you meant sumifs... Countifs only return the count not the sum
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.
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!
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!
Thanks for the suggestion!
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?
@@derleichtglaubige4415 I know man, but at times it is useful when researching data from American projects or papers
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....
Your tips are great! Thank you very much Leila!
Excellent! More excel for engineers please...
Accountants using Excel: all sorts of numeric calculations
Me: that's OK
Enginners using Excel: only text oriented formulas
Me: wait, what?
Whole hearted thanks for making this video and get rid of complex computaton methods to pursue BoM based activities
Our pleasure!
Thanks Leila most of them are really useful.
I like Len!...."find"in Left
thanks, im a mechl engr, an addition to my excel learning specially im handling Heavy equip.
The REPT command is new to me. Thank you for sharing! I will definitely be using that in the future!
Extremely useful and didactic (as always).
Thanks Leila!!!
This was BRILLIANT. Especially the last formula. I wish I had of known these a few years ago 😁 Thank you so much, I love your tutorials.
Very useful.... 15 minutes so well spent in expanding our learning.
I use most all of those methods every day, often on Bill of materials just like your demonstrations. Great Job!
👍
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!
👍
We do use macros as well. There some redundant reports to be run. A detailed macros video with complex macros examples would help.
Good sharing, my sister
Thank you! I will be appreciated to see a video for the lookup function!
Awesome video Leila! I’d love to see those Lookup formulas explained better in the same BOM context!
As a mechanical engineer/ designer i can say I use these functions and a few others frequently
Great video
Thank you!
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.
Glad you liked it!
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.
@@Info-God that would be great. Let’s keep in touch.
@@caciocavalloster Let your company know if they are interested. I make a list of potential clients.
That was good. I work with BOMs all the time, and use many of these functions. I learned a few others.
Thank you for this video … i am a civil engineer and i use OFFSET and MATCH functions a lot !
Thanks for the feedback!
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.
Great, I used some of them working as cost accountant when playing with BOMs or when extracting some values
I would Love to see more about Lookup. More detailded and compare to bonus thjresholds, within Procurement for example.
You are Awesome by the way
I just used trim function in a table i extracted from pdf it works like magic Thanks
Wow.. I never used Rept and lookup functions.. It is really amazing.. Thank you for the video..
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 !
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.
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😊👍
Thanks for sharing!
Thank you Mrs. Leila for this great job.
I liked REPT & TRIM functions
👍
Well explained, used while creating engineering BOM.
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.
Hope it will come in handy for you!
Amazing! My head just exploded. The more I learn about excel, the more I realize that: a. I know very little; & b. that excel is likely the most misused MS programme there is.
A like and share before I start watching the video... Thank you alot Leila for your informative video and your continuous efforts are highly appreciated. Proud to be a student at your MS Excel school and XELPLUS... 😊😊
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.
Thank you, very useful
Very beneficial for mechanical engineers,, thanks
Thank you Leila. The lookup function is very confusing though. I need some more tutorial on that 😁
Thank you!!
Leila your lessons is marvelous!
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.
I am mechanical engineer worked for automotive and locomotive, same data i worked but not aware of this functions
Thanks from india ❤
Great 👍
Excellent.... Keep posting such valuable videos
Thank you! Thank you! Thank you!
Your videos are excellent, Leila.
@Leila, you are just perfect with what ever you deliver in this channel. I always go nuts in getting the right data for me from these damm....excel files which I get from customers. This is something really quick and easy. Thanks a lot for sharing the tricks. You are the best ! Merry Christmas 🤶 🎄 ❤. #Love from INDIA 🇮🇳
Hi Leila. Great examples for engineers and others! Thanks always for sharing your useful and creative approach to problem solving. Thumbs up!!
Many thanks Wayne! All the best for 2022!
Great videos, great tips!
Thank you for this video and please do in the future a video about lookup function.
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.
Muy útil. Thank you
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!
Then this comment answer my question, (what is BOM)!.. There you mention it engineer!!
My pleasure, Marcelo!
Thank you Mme. Leila, this is extremely educational.
Thank you!
I wish you do more video for engineers!
Your video are very helpfull :)
Please make a video about all "LOOKUP" function :)
Noted!
Thank you so much
Excellent, thankyou
Excellent video ❤️, need a dedicated video on lookup function.. I really don't know the potential of lookup function. Keep rocking Leila
so useful 👍, thanks always. and please explain the lookup equation
Thank you so much.. 👍
Thanks Leila for the great videos. It would be nice if you kindly make a separate clip on lookup & vlookup with few application on various data sets Thank you very much!