Collaborate on Bill Excel Sheet for Engineering with Ease Using airSlate SignNow

Bill excel sheet for Engineering

This video is for all new engineers out there, especially mechanical engineers who use Excel at work. So make sure that you go through the Excel functions that are show this video, because it's quite likely that you're gonna need them. Now, the great thing is that these functions work on all Excel versions. And you're gonna be surprised when you see some of these, because I haven't covered of these functions before in this channel. And if you're wondering, how on earth do I know what engineers need? I don't, I'm not an engineer, but I have an engineer in the team. So he showed me how he uses Excel. And I was actually very surprised about some of the functions that he had. It's quite different to the functions, the finance professional needs. Now, although you never know, because it really depends on the situation. So check it out. And especially if you're an engineer, because your life is just gonna get easier. Here's our sample database. We have the bill of material level and the part number for each level. So this sense, a parent child product relationship, this part number belongs to this product. These belong to this one. Now, when we go further down here, we come across these twos. They don't belong to this three here. They don't belong to the two or the three. They belong to this product up here. So you can and visualize this as a hierarchy that looks like this. Then here we have the part description material category, the weight, whether it was made or bought and the quantity required. Now let's start working on this data set function. Number one is the rep function. This function allows you to repeat values. And as often as you want, so here we wanna get the indent part number because remember part number is organized like a hierarchy. We want to visually see this using indentation. So basically if you were gonna do it manually, we'd be copying this over. And then we're gonna take a look at the bomb level and see if it needs indentation or not. If it's on the top of a hierarchy or not. So this one isn't, so we don't need to do anything. This one is in the first level, just below this one. So we're gonna add one indentation. This one, two, these ones are also two. So they belong to this one here. Then this one is one, this two and so on, right? So you can do this manually, but you can imagine you have a lot of data here. You definitely don't wanna do this manually. So what you can do is use the R E PT function. It's a function that repeats things. First, you define the text that you wanna repeat. So add a space in quotation marks. Next, how many times do we wanna repeat this? So let's say by default, I wanna have three spaces. I'm gonna add three, but because it depends on the level here. I'm gonna multiply this with this number close bracket. Now we're gonna combine this with the am. Present with the part number. When I press enter, this doesn't have any indentation because three times zero is zero. But when I send this down, we get the correct indentation for each level. Next one, the trim function. So sometimes your PLM software might give you part numbers that are already indented. Now these can give you problems when you use looka formulas, right? Because you have all these additional spaces that you don't need, and you wanna strip them off to do that. You can use the trim function. All you need is your value, close bracket, press enter. And it trims off any additional space is that you have before anything you have after. And in case you have additional spaces in between, they are trimmed off as well. Next up we have Thelen and substitute functions. Sometimes your data extract might give you the item level in this format, and you wanna figure out the bill of material number. Now, the logic for this is that if you just have one character, you are on the top node. So this is gonna be zero. If you have one period in there and one character after the period, then you're in the first level. In this case, you are in the second level, actually in all these three cases, you are in the second level here. We're back to level one down here because we have three periods. Here. We are in level three. That's the logic behind this. But again, we don't wanna do this manually. We wanna figure out a formula that does this. That's where these substitute and land functions come into play. Now we're gonna start with substitute. I can show you what that does. First thing you need is the text. That's the value that you wanna use substitution on. So in this case, it's this one, what's the old text that we wanna substitute. Well, I wanna take the period here and I wanna substitute that with new text, the new text is just nothing. So basically I'm removing that peer. When I close this, I get to replace all instances of the period with nothing. So in this case, while I don't have any period, I'm just gonna get a one. But in these cases, notice that period is gone. Now, how can I get the appropriate level number? Well, I can use the length function to do that because what the lens function does is it tells me how many characters this text has. Now. This text is the result of my substitute function. So when I close bracket and presenter, this one has just one character, right? There was just a one in there. This one will have two. This is three. That last one is three as well. Okay. This one is four. And so on now to get the correct bill of material note number, I'm gonna use the lens function here, grab the length of the item level and deduct that from the version where I remove the period. Now, when I presenter, I get the correct bill of material. Number. Next is the left function with the left function. You can grab the left side of a longer text. So in this case, we have the part identity and we wanna split this into part number and description. Part number should be the number that we see here. We can use the left function for this. What we need is the text. That's our value right here. Then we can define the number of characters. Now, if this is fixed, in your case, you can just put that number in here. So in this case, I wanna extract the first 10 characters I to 10 close bracket presenter, and I have my part number. Next function is fine. So let's assume that our part number could have different characters. The logic here is to grab everything that comes before the comma. You can use the find function to do that. So instead of defining that, we want 10 characters. We're gonna look for the comma using defined function. The text that we wanna find is a comma. I'm gonna put it in quotation marks within text. That's our cell right here. Start number by default is gonna start looking from the first character are just gonna leave that empty close bracket, press enter. And now we get our part number, including that comma, because what find does, if I debug this to show you the underlying value, we can see exactly how many characters is behind this. I'm gonna press F nine and we can see 11. That means that the comma is the 11th character in this text here. So I'm just gonna press control Z to go back. What we need to do is deduct one from this. So minus one presenter, and now we have our part numbers. Now let's take a look at the right function, the right works in a similar way to the left function, except we get to strip out the right side of the text. So in this case, if I start off with right, my text is right here and I say, I wanna get 1, 2, 3, 4, 5, 6, 7, the last seven characters. I'm gonna end up with trailer, but this is not thing I can pull down because I have different number of last characters. What I wanna do is figure out where that first comma is, and then strip out everything that comes after that first comma. I wanna make this seven here, dynamic. Now I can use the lens function that we saw before to get the total care characters that we have right here. Once we have to total characters, we are going to deduct the number of characters up to that comma here. So we are gonna use the find function. Look for the comma. Look for it right here. And start number by default is to start looking from the beginning and we're gonna close the bracket here, press enter. We get trailer for this. Does it work? If I send this down and it works next, look up function. This is one of the original looka functions in Excel. Now it syntax is simple, but you can make this function a bit complex, especially if you wanted to do complex things. So in this case, what we wanna do is find the immediate parent part. What this means is that for this first part number here, where we are on the top node, doesn't have a parent who Don want anything for this one. The immediate parent of this is this right here. Then for these, the immediate parent is this one right here. Now it's not always the label that's above it because take a look at these ones. The immediate parent is not three because that's below it. Two is adjacent to it. So we have to go all the way back up to find the immediate parent. And that's this one right here, right? So that's ultimately the result that we wanna get, but we wanna do it with a function. Now, the look up function here is a bit complex. So if you really wanna understand how this works comment below and I'll consider making it video about this, you're gonna start off with lookup. Uh, if you're wondering why I'm writing it here, that's because the first cell here doesn't have a parent. So if I write my formula here, I'm gonna end up with an error anyway, just to make sure I have everything correctly done with the syntax and so on. I write it here and then we can copy it down and up as well. So the look of value is gonna be a two. I'll explain in a second. Why the look up vector is one divided by we're gonna highlight this cell from the beginning to right where we are, right? So to right where we're writing the formula. But because we want this range to expand as we pull this down so that we can always look back to see where the immediate parent was. I'm gonna fix the, a two reference by pressing F four and putting the dollar signs there. Now I wanna check if this equals this minus one. Now all of this needs to be in brackets. Okay? So that's my look up vector. And my result vector is this. And again, I have to be consistent with the look up vector reference. I'm gonna fix the B2 reference by pressing F four. Now, when I close bracket and press enter, I get the immediate parent right here. Does it work if I send this down? Is this parent correct? It's the value right here, right? So this works perfectly well. Now just a quick explanation of why a two, well, what this part returns. If I press F nine to debug this, you see that we get one or an error. Two is just a value that's higher than one. It could be any value that's higher than a one. So we're gonna get the same answers. If you put a three, if you put a four or anything that higher you'll get the same result. Next up is the if error function. So I wrote the look of function starting from the third row here, but it would actually be correct to write it from the first row and then send this down. You wanna have a consistent formula throughout your column, but you might end up with errors. Whenever your level is zero to overcome this, you can use the, if error functions. So you're gonna start off with if error open bracket, your value is in this case, the formula that we have here, and then we can define the value we wanna get. In case we have an error and I just wanna put dash here in quotation marks, closed bracket presenter. Now I either get the dash or I get the immediate parent part. Now let's take a look at the if function. So let's say we wanna check whether we are at a top level or not. So whether the bill of material level is zero or not here, we can use the, if function to do this check and you can use it to do any type of checks that are based on <affirmative> and logical test. So our logical test here is to take this number and see, is it equal to a zero? Now, if it is true. So if it is equal to a zero, what do we want returned? Let's say, I want, yes, returned else. What do I wanna return? If it's false, I wanna return and no close bracket percenter. Now this way I can quickly identify whether I'm at a top level or not as a bonus. Here's a formula that brings together three of our important functions into one function. Here. We calculate extended quantity now, just so that we can see better, I'm gonna drag it and drop it right here beside. So what this does is it calculates the quantity of each part based on the quantity that is needed by the parent as well. So let me explain for this part here. The quantity is two. The parent of this one has a quantity of four. So the extended quantity is two times four. It's eight. Now, when it comes to this one, the quantity is two. The extended quantity is two times eight. That's 16 to get this to calculate correctly. We've used a similar approach to the look up approach with here to get the immediate parent part. We have if error for error, hand link, and in case we happen to be at the top level, we are just gonna return the quantity that we have in the age column. I hope you found some value in this session, whether you're an engineer or not. So before you leave, let me know in the comments, what function you really liked. Thank you for watching subscribe. If you are unsubscribed and I'm gonna see you in the next video.