10 Excel Functions You NEED to KNOW as Engineers!

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.

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.

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:

🚩Let’s connect on social:

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

[dchall]

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.

[LunaStarFire]

(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.

[farzanmoha]

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 🙌

[gregmurphygolf]

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!

[SuSaNa_PT]

Eng here. Please consider to extend the engineer sessions :) helpful.

[andrewcharlesmoss]

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.

[Purvi_kankanal]

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.

[deanwoods]

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!

[pauldadzie]

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.

[bigk]

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

[jludtmanable]

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.

[legioner]

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.

[hsh]

THANK YOU Leila!!

I never miss your videos and will definitely not miss this one as I’m a mechanical engineer myself.
Thank you 🙏

[Heinzlif]

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.

[AlexKasper]

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.

[xaquison]

Thank you! Very useful and practical. Love how you use the REPT function, very ingenious.

[bourezahkarim]

Also the MID function can be added to this beautiful bunch for engineers.
Thanks for sharing.

[jackhoff]

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!

[Nicolas-jxoo]

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

[awmawm]

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.