Search

5 Excel Formulas That Could Transform Your Engineering Spreadsheet

Although Excel can be used for quick calculations, tabulating data, drawing shapes, it can be very powerful as it has the ability to run macros and update charts automatically from input data. Intelligent functions can perform linear interpolation on a set of data and equations can also be fitted to data using the ‘linest’ function. Regression analysis can also be useful in statistics.


Excel is a very versatile piece of software that has a range of applications. As a mechanical engineer, there’s not a day that goes by without me using it, even if it’s just to perform simple tasks like calculations or recording data. It can make long and repetitive jobs much simpler and quicker, like MATLAB. Using macros can save you a lot of time too and they can be used to automatically perform a series of commands you regularly use at just the click of a button.


In this blog, we will explore some intelligent formulas that can transform your engineering spreadsheet.

1. =INDEX()MATCH()


The Index Match formula is one of the most used formulas in our excel spreadsheets. This formula allows you to pull data in from data tabulated results or other tables, you can automatically pull through this data and ensure it is always updated.


In an engineering context this may be specific data from a Bill of Material to find a set price, supplier, material etc. It works in a similar way to the HLOOKUP and VLOOKUP formulas but provides additional functionality and control over the data you are trying to output.


To use this formula you need to index the array of data which you wish to output and then use the match formula from one variable against the one in the table which the data has been arrayed from. To see an example, please check out the example and line of the formula below:

2. =LEFT()


This simple formula can be very valuable and robust in an engineering environment. The most common time that we have used it is when extracting data out to a bill of material in an excel spreadsheet. A common example of where it will be used is when the first x amount of numbers or letters are used to define the part and the final x amount of numbers or letters are used to define the revision that the build is at.


If there is then a situation where only the first x number of letters are required to compare against another table using the INDEX MATCH formula or a VLOOKUP/HLOOKUP, only the first x number of numbers or letters need to be used.


This can be seen below in an example Bill of Material for a random part:


3. IF Statements


=IF() =IFS() =IFNA() =IFERROR()


IF statements are one of the easier logic based statements that can be used in Excel to output the data you wish to.This function allows you to have 2 outputs depending on what you wish to have and what else could occur.


Using IF statements can be very useful when nested within each other allowing for multiple options, however this can only occur until 64 levels. It can also be useful to use in conjunction with other simple syntax and mathematical syntax.


Below is a very simple example of the 4 basic IF statements that can be used in Excel and the formulas used to create them. In these examples, they are purely based on cell values from a simple and random table that has been created. For the IFNA and IFERROR, the syntax causing the errors has been input on purpose.

4. =CONVERT()


In engineering there are many different units that can be used. The SI (Systeme International) is a metric system that defines such units. It states that all units of measurement can be built off the 7 base units which we will delve into deeper in future website training and blogs.


The convert formula allows you to convert engineering units from one to another.  practical example of this would be measuring an item in meters and then requiring conversion of that into the SI unit which is millimeters.


Through the use of tables, vast quantities of units can be easily converted using this simple formula. An example of the formula can be seen below. In this example we have calculated the speed in kph based on rider weight (with many assumptions) and then used the Convert formula to m/s - an SI unit. Unfortunately Excel does not recognise kph as a unit (maybe because it is not included under any numerical systems) so we first have to convert it into mph. From that point we use the simple formula of:


=CONVERT(SELECT RANGE OR CELL,”mph”,”m/sec”)


This then gives us the output table that can be seen below:


5. IS Statements


=ISBLANK =ISERROR =ISTEXT =ISNUMBER


IS statements are another logic based formula and can be very useful in cleaning up an excel sheet that may have blank cells, errors or specific numbers and text.


These statements are quite self explanatory and work based on a similar syntax as the IF statements that we saw above. As with IF statements, IS statements can be used in conjunction with other formulas to overwrite any unwanted outputs. It can also be used in conjunction with IF statements as you may not be looking to only have a Boolean output for a specific cell.


There are a number of other IS statements that can be used but we will focus on the most commonly used which are the ISBLANK, ISERROR, ISTEXT and ISNUMBER. The following table gives examples of some of the main IS statements with some random input data. Is stated above, a boolean logic output is given, however, by using additional formulas in sequence, these boolean outputs can be then be used to give the output that you wish to present in their spreadsheet.


These are just 5 examples of formulas to help with your engineering spreadsheets, there are many more. As with many other engineering techniques, it is best to learn by doing. You will find that as you utilise such formulas, you will begin to find new ones that may be of better use to yourself. Spreadsheets are widely used in engineering from data validation and sorting to creating Bill of Materials and supply chain management. Additionally, many computer-aided engineering software can run in conjunction with Microsoft Excel. An example of this is Catia V5 Design Tables. We hope to provide examples of this and other excel integration examples in the near future. If you have found any useful formulas in your own applications, let us know through any of our social media platforms or in the comments section.



Luke T Seal Engineering