(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.
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.
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 🙌
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?
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!
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!
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.
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.
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
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.
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
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
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!
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.
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.
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....
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.
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.
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.
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!
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 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.
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 !
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!
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 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.
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.
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.
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
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 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.
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.
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.
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…..👏👏👏
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
I'm a water resources engineer and I've used all of those many times except for the REPT and LOOKUP functions. Please do a video on the LOOKUP function. I'm a fan of XLOOKUP thanks to your videos. Also, I noticed that the "engineer" functions are still a bit business oriented. Most of my use in Excel is strictly numerical calculations. I'd love to see a video to that extent.
classroom speak the label DOS disk operating system in speak english utility year 2022 / 2023 sys data com master the fact index presentasi personal computer 2022 / 2023 🏛🌍🌎🌏🏛🏪🏫🏬🏭🏯🏰💒🏩🏨🏧🏦🏥🏤🏣
"Hello! I watched your video on calculating the Extended Quantity, and it was really helpful. Considering that I want to apply this formula to a table with more than 10.000 rows, I have a question regarding that: Is there a possibility that the formula might get disrupted if the table is sorted based on specific criteria? How can I ensure the accuracy of the calculations after sorting? Thank you in advance for your response.
I am a mechanical engineer. I love excel and use it very frequently. But the functions I use most often are: SUMIF (Create a second array with summed values from the first one) VLOOKUP and COLUMN to search in arrays INDEX and MATCH because... Of course I do ROWS to count sequence numbers in tables and add a new number when adding a new line with Tab, and more. Generally, I use it excel for searching in 2D arrays based on input values and interpolate between available values. Hope this helps. P.S. I really like the use of REPT and you definitely should do a video on LOOKUP...
Grab the file I used in the video from here 👉 pages.xelplus.com/excel-engineers-file
(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.
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.
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 😊
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
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.
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!
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.
Glad it was helpful, Paul!
Eng here. Please consider to extend the engineer sessions :) helpful.
Noted! Glad it was helpful.
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.
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
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.
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
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
Thank you! Very useful and practical. Love how you use the REPT function, very ingenious.
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!
Excellent! More excel for engineers please...
Thank you for this video … i am a civil engineer and i use OFFSET and MATCH functions a lot !
Thanks for the feedback!
THANK YOU Leila!!
I never miss your videos and will definitely not miss this one as I’m a mechanical engineer myself.
Thank you 🙏
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.
Amazing video, I am always learning something new every time. The part using the LEFT and RIGHT functions combined with FIND was my favorite.
The REPT command is new to me. Thank you for sharing! I will definitely be using that in the future!
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.
Great tutorial. As a novice engineer I have much to learn and apply. Thanks for the excellent video.
This is all I ever needed excel for. Working on BOMs will be so much easier. Thank you. More Please
I use most all of those methods every day, often on Bill of materials just like your demonstrations. Great Job!
👍
Whole hearted thanks for making this video and get rid of complex computaton methods to pursue BoM based activities
Our pleasure!
I'm not an engineer, but the video was very informative, thanks Leila!
Rept, substitute, and Len functions were new. Plus the way you integrate them is awesome. Thank you Leila Sensei.
Our pleasure!
Thank you! I will be appreciated to see a video for the lookup function!
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....
Thanks Leila most of them are really useful.
I like Len!...."find"in Left
Very useful.... 15 minutes so well spent in expanding our learning.
Lots of neat tricks in here. This video is a keeper!
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.
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!
Thank you Mrs. Leila for this great job.
I liked REPT & TRIM functions
👍
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.
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
Thanks Leila, I’m not an engineer, still found these extremely useful.
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
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!
👍
Wow.. I never used Rept and lookup functions.. It is really amazing.. Thank you for the 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 😊
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?
Your tips are great! Thank you very much Leila!
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!
Thank you so much for your great inputs..!! I loved TRIM function and Lookup Function this will definitely help my day today activity..!!
This was great! Love learning new functions in Excel! Thanks!
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 !
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!
thanks, im a mechl engr, an addition to my excel learning specially im handling Heavy equip.
I just used trim function in a table i extracted from pdf it works like magic Thanks
As a mechanical engineer/ designer i can say I use these functions and a few others frequently
Great video
Thank you!
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
That was good. I work with BOMs all the time, and use many of these functions. I learned a few others.
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!
Rept, substitute, and Len functions are so useful for P6 Schedule developers. Much appriciated.
Glad it was helpful!
Thank you 👍😊 Leila
Yes’m, another video on this lookup function would be helpful! I can see how this would be useful
Noted!
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!
Great, I used some of them working as cost accountant when playing with BOMs or when extracting some values
Excellent.... Keep posting such valuable videos
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.
Awesome video Leila! I’d love to see those Lookup formulas explained better in the same BOM context!
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.
Thank you for the kind feedback!
🙏for every excel tips👌👌🙏🙏from india🙏
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.
Well explained, used while creating engineering BOM.
Very beneficial for mechanical engineers,, thanks
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
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 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.
My way of coping with this is to give name to cells , then your formula is more readable with "maxPressure*length" than "A6*H12"
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.
Really useful channel Leila, thank you and blessings
Glad you think so!
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.
Thandk leila... I had been working through BOM like for 6 month your tip and help😃😃😃😃😃
Thank you! Thank you! Thank you!
Your videos are excellent, Leila.
Thank you Leila. The lookup function is very confusing though. I need some more tutorial on that 😁
Leila, you are wonderful. Have a nice Christmas and Happy new year.
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.
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…..👏👏👏
Noted!
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
Thanks for sharing!
REPT really helped. Thanks a lot Leila
Our pleasure :)
Leila your lessons is marvelous!
Excellent video ❤️, need a dedicated video on lookup function.. I really don't know the potential of lookup function. Keep rocking Leila
I'm a water resources engineer and I've used all of those many times except for the REPT and LOOKUP functions. Please do a video on the LOOKUP function. I'm a fan of XLOOKUP thanks to your videos.
Also, I noticed that the "engineer" functions are still a bit business oriented. Most of my use in Excel is strictly numerical calculations. I'd love to see a video to that extent.
Thanks for the feedback, Derek! We'll see what we can come up with for LOOKUP :)
classroom speak the label DOS disk operating system in speak english utility year 2022 / 2023 sys data com master the fact index presentasi personal computer 2022 / 2023 🏛🌍🌎🌏🏛🏪🏫🏬🏭🏯🏰💒🏩🏨🏧🏦🏥🏤🏣
what graphs do you recommend to learn for water resources ?
"Hello! I watched your video on calculating the Extended Quantity, and it was really helpful. Considering that I want to apply this formula to a table with more than 10.000 rows, I have a question regarding that: Is there a possibility that the formula might get disrupted if the table is sorted based on specific criteria? How can I ensure the accuracy of the calculations after sorting? Thank you in advance for your response.
Thanks Leila ! We would be grateful if you could make a video on look up function. It seems to be really useful. Thanks a lot !
Noted! We'll see what we can come up with.
Though the past year was quite difficult, we learned a lot and we survived!
Merry Christmas and advance Happy New Year!
Very true! Merry Christmas to you too and a great 2022!
Really this video is very helpful for engineers , thank you very much Leila .
You're very welcome!
I am a mechanical engineer. I love excel and use it very frequently. But the functions I use most often are:
SUMIF (Create a second array with summed values from the first one)
VLOOKUP and COLUMN to search in arrays
INDEX and MATCH because... Of course I do
ROWS to count sequence numbers in tables and add a new number when adding a new line with Tab, and more.
Generally, I use it excel for searching in 2D arrays based on input values and interpolate between available values. Hope this helps.
P.S. I really like the use of REPT and you definitely should do a video on LOOKUP...
Thank you for this video and please do in the future a video about lookup function.
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!
Thank you!
I wish you do more video for engineers!
Your video are very helpfull :)
I liked the REPT and TRIM function
The Rept Function really got my attention
Thank you Mme. Leila, this is extremely educational.
Greatful for the 1.4M sub mark, keep going you inspire some of us