Learning Materials

Features

Discover

# Excel Formulas

Explore the intricacy of Excel formulas and uncover their immense significance in the field of Engineering with this guide. You'll delve into the meanings and applications of key terms, revealing how mastering Excel can power up your engineering projects. Learn about simple to complex Excel formulas examples, discover pivotal percentage formulas specific to engineering and see an elaborate list of Excel formulas for engineering purposes. Furthermore, gain a better grasp of Sum, Average and Count Excel formulas and their effectiveness in engineering calculations. The understanding of 'If Then' function and its usage is also unveiled. Take your engineering skills to another level with this comprehensive exploration of Excel formulas.

## Understanding Excel Formulas: Definition and Importance

Our journey into the fascinating world of Excel Formulas begins with a firm grasp of what they are and why they are vital in the engineering profession.

### Meaning of Excel Formulas for Professional Engineering

Excel itself is an incredibly versatile tool used by engineers around the globe, but it's the formulas that truly unlock its potential. A formula in Excel is essentially a way to perform computations or operations on data within the spreadsheet.

They can be as straightforward as adding two numbers together or as complex as creating a dynamic spreadsheet model for a significant project. Engineers use these formulas to help calculate results and analyse data, providing important quantitative measures that inform decision making.

From designing structures using mathematical modelling to analysing data from experiments, engineers can cut down on time-consuming manual calculations and improve accuracy with Excel Formulas.

Here are a few examples of how you might use Excel Formulas in engineering:

• Calculating force vectors
• Filling in cells based on specific criteria
• Analysing statistical data
• Predicting future data points using regression

Consider an example where you want to calculate the result of an experiment. You have a set of data and want to calculate the average. You could sum up all the data points and divide by the total number, but with an Excel formula, you just need to input '=AVERAGE(range of data cells)' and excel will do all the work for you.

#### Key terms and functions in Excel Formulas

There are many terms and functions involved in Excel Formulas, each playing a unique role in making these formulas work.

1. Cell Reference : This refers to the alphanumeric address of a cell, for example, B3 or C5.

2. Operators : These include the common mathematical operators such as + for addition, - for subtraction, * for multiplication, and / for division.

3. Functions : Functions are predefined formulas that perform a specific task. There are hundreds of functions in Excel, like SUM, AVERAGE, COUNT, and so on.

4. Formula Bar : The area where you enter and edit data in the selected cell or the active formula.

Here are a few essential formulas used in Excel:
 SUM Adds up all the numbers in a range of cells. AVERAGE Calculates the average of numbers in a range of cells. COUNT Counts the number of cells that contain numbers in a range of cells.
Excel Formulas can be a powerful tool when understood and used effectively. Once you master these core concepts, you'll find a world of possibilities open up in your engineering work.

## Mastering Excel Formulas: A Comprehensive List and Examples

Dive deeper into the sea of Excel formulas. Whether you're just starting your engineering journey or looking to brush up your Excel skills, this extensive list and array of examples will be a powerful resource.

### Excel Formulas Examples: Simple to Complex

From basic calculation formulas to more sophisticated functions for data analysis, Excel indeed has a rich library of functions that can be invaluable to your engineering work. Let's start with some simple formulas:
=SUM(A1:B2) //This formula will add all the numbers in the range from cell A1 to cell B2.
=AVERAGE(A1:A10) //This formula will calculate the average of the numbers in the cells from A1 to A10.
=COUNT(A1:A20) //This formula will count the number of cells in a range that contain numerical data.

Moving on, you could manipulate text, extract specific information, and combine data from different cells using functions such as LEFT, RIGHT, MID, CONCATENATE, and others.
=LEFT(A1, 5) //This formula will extract the first 5 characters from the data in cell A1.
=CONCATENATE(A1, " ", B1) //This formula will join data from cells A1 and B1 with a space (" ") in between.

Beyond these, you'll find a category of more complex formulas known as array formulas. Array formulas enable you to perform operations that involve multiple ranges or arrays of data rather than single data points. Here's an example:
{=MAX(A1:A10*B1:B10)} //This is an array formula. It multiplies corresponding values in the arrays A1:A10 and B1:B10 and then identifies the maximum value in the resultant array.

Remember, array formulas are entered using CTRL+SHIFT+ENTER and, thus, are encased in braces {}.

#### The Extensive List of Excel Formulas for Engineering Purposes

There are several Excel formulas specifically beneficial for engineering actions. The powerful 'SUMPRODUCT' function can perform calculations on corresponding values in multiple arrays. For example,
=SUMPRODUCT(A1:A3,B1:B3)

This formula multiplies each corresponding pair of numbers from two arrays and then returns the sum of all those products. With 'CONVERT', you can convert a number from one measurement system to another.
=CONVERT(100, "lbm", "kg")

This formula converts 100 pounds into kilograms. The 'LOGEST' function can help you with regression analysis. It returns the parameters of an exponential trend for existing data.
=LOGEST(B2:B10, A2:A10, TRUE, TRUE)

This calculates and predicts growth based on the natural logarithm of 'y.' Input is carried out based on column 'B' values (dependent variables) and 'A' (independent variables), with the last two TRUE arguments allowing the function to add a constant to the equation and calculate the summary output. This is just the tip of the iceberg. Several other functions, like 'TREND', 'GROWTH', 'TRANSPOSE', 'MINVERSE', and many more, play distinct roles in furthering the capabilities of Excel in engineering fields.

### Essential Excel Percentage Formulas for Engineering

As engineers, managing and interpreting percentages is a common task. It can relate to anything, from calculating error margins and efficiencies to estimating project completions. Thankfully in Excel, there are multiple ways to calculate percentages. The most basic way to calculate a percentage is dividing the part by the whole and then multiplying the result by 100.
=(A2/A3)*100

This formula calculates the percentage that 'A2' is of 'A3'. To increase a number by a certain percentage, you could use a formula like this:
=A1* (1+B1)

Here, 'B1' is the percentage to increase 'A1'. If you want to decrease a number by a certain percentage, the formula will look like this:
=A1* (1-B1)

You can also calculate the percentage change between two numbers with this type of formula:
=(B1-A1)/A1

This formula calculates the percentage change from 'A1' to 'B1'. In engineering, a common use of percentage formulas may be to understand changes in data over time or modify certain parameters by a percentage amount. Understanding and mastering these Excel formulas can go a long way in streamlining your engineering tasks, improving productivity and data accuracy, and making the overall process more efficient.

## Functions Explained: The Effectiveness of Sum, Average and Count Excel Formulas in Engineering

Engineering professions require immense attention to detail, particularly with voluminous data calculations and analyses. In this regard, the Excel spreadsheet becomes an essential tool, and amongst its numerous features, the Sum, Average, and Count functions play a pivotal role. These basic Excel functions help simplify the process of calculating and analysing mass data, bringing both efficiency and accuracy to an engineer's work.

### Getting the Sum Right: The Importance and Application of Sum Excel Formulas

The Sum function is a fundamental tool in Excel, and understanding its proficiency is vital for every engineer. Sum in Excel is used to add together the numbers provided as arguments. Whether it’s determining the total weight of materials needed for a project, summing the costs of individual components for budgetary decisions, or adding experimental data points, the Sum function provides a convenient means of achieving these calculations in a timely manner.

To use the formula in Excel, you need to type an equals symbol, followed by 'SUM', and inside a pair of brackets, provide the range of cells you want to add. For example, if you have a list of numbers in cells A1 to A10 and you want to get the total, you would write:

=SUM(A1:A10)


The result will be the sum of the values in cells from A1 to A10.

Another illustrative function is the use of Sum in combination with the 'If' function. When engineers need to sum data that meets a certain condition, a simple Sum function isn’t sufficient. In such cases, the SUMIF function comes into play:

=SUMIF(B2:B9,">20",A2:A9)


In this example, the SUMIF function sums up all values in cells A2 to A9 that correspond to values over 20 in cells from B2 to B9.

#### The 'If Then' Function: Understanding and Using the If Then Excel Formula

The 'If Then' function provides a logical test wherein it checks if a certain condition is met and then performs an action based on the result. This function can be incredibly useful in engineering where data analysis often involves certain criteria or conditions.

The basic format of the Excel 'IF' function is:

=IF(logical_test, value_if_true, value_if_false)


If the logical_test evaluates as TRUE, the function returns value_if_true. If it evaluates as FALSE, it returns value_if_false.

Imagine you have two columns of data representing load tests on different materials. The first column shows the type of material and the second records whether it was a 'pass' or 'fail'. If you wanted to find out how many 'Steel' samples passed the test, your formula might look like this:

=COUNTIFS(A1:A10, "Steel", B1:B10, "Pass")


This formula counts only the rows where the first column is 'Steel' and the second column is 'Pass.'

### Getting the Average: Average Excel Formulas in Engineering Calculations

In engineering, you often come across situations where you have a large dataset, and you need to find a central or common value. This is where the Average function comes in handy.

Essentially, the Average function calculates the arithmetic mean of a set of numbers. This can be useful in a variety of engineering scenarios, such as calculating average temperatures in environmental engineering studies or average stresses in a structure in civil engineering.

The syntax of the Average function is:

=AVERAGE(number1, [number2], …)


Here, 'number1, number2, …' are the arguments, which can be numbers or names, arrays, or references that contain numbers.

• Up to 255 numbers can be included in the list of arguments.
• Cells with zero are included in the average calculation.
• Empty cells or cells containing text or logical values are ignored.

#### Count it Right: The Role and Usage of Count Excel Formulas in Engineering

In engineering, ensuring data accuracy and relevance is critical. The Count Excel function plays an integral role here. Count allows you to count the number of cells in a range that contain numeric values. This is perfect when you want to quickly gauge how many data points you’re dealing with, be it test results, measurements or readings.

The basic Count function syntax goes as follows:

=COUNT(value1, [value2], …)


Using this, you can quickly get the count of numbers in a range. For instance,

=COUNT(A1:A10)


calculates the number of cells containing numbers from A1 through A10.

Beyond this, more advanced functions like COUNTIF and COUNTIFS can be utilised when you need to count cells based on specific criteria, hence adding an additional layer of capability to your data analysis in Excel.

In conclusion, Excel Formulas - SUM, AVERAGE, and COUNT, have a massive potential to streamline your engineering tasks by simplifying complex computations and saving valuable time.

## Excel Formulas - Key takeaways

• Excel formulas are used to perform computations or operations on data within the spreadsheet and are vital tools for engineers.
• Some important terms in Excel Formulas include Cell Reference, Operators, Functions, and Formula Bar.
• Basic Excel Formulas used in engineering include SUM, AVERAGE, and COUNT functions.
• Excel formulas can range from simple calculates (like adding numbers) to complex functions like SUMPRODUCT, CONVERT, and LOGEST.
• Percentage formulas in Excel can aid engineers in calculations related to error margins, efficiencies, and project estimations.
• The 'If Then' function in Excel allows for a logical test to be performed and is useful for engineering data analysis involving certain criteria or conditions.

#### Flashcards in Excel Formulas 12

###### Learn with 12 Excel Formulas flashcards in the free StudySmarter app

We have 14,000 flashcards about Dynamic Landscapes.

What are the formulas for percentage in Excel?
To calculate percentage in Excel, the formula is =part/total. Next, format the cell as a percentage by selecting the cell, click the percentage symbol (%) in the 'Number' group on the 'Home' tab. The formula will return the percentage value.
What is the 'IF' formula in Excel?
The IF formula in Excel is a logical function that allows you to make logical comparisons between a value and what you expect. It returns one value if a specified condition evaluates to true and another value if it evaluates to false.
What is the formula for subtraction in Excel?
The formula for subtraction in Excel is very straightforward. You simply use the minus (-) sign. For example, if you want to subtract B1 from A1, the formula would be "=A1-B1".
How can one display a formula in Excel? Please write in UK English.
To show a formula in Excel, select the cell with the formula, go to the 'Formulas' tab and click on 'Show Formulas'. Alternatively, you can use the shortcut Ctrl + ` (the key above Tab).
How can one hide a formula in Excel?
To hide a formula in Excel, first select the cell containing the formula. Then, go to the 'Format Cells' dialog box (press Ctrl+1), navigate to the 'Protection' tab, check both 'Hidden' and 'Locked' boxes, and click 'OK'. Finally, protect the worksheet under 'Review' -> 'Protect Sheet'.

## Test your knowledge with multiple choice flashcards

How can you convert a number from one measurement system to another in Excel?

What are the functionalities of the Average and Count functions in Excel?

What are some essential functions used in Excel Formulas?

StudySmarter is a globally recognized educational technology company, offering a holistic learning platform designed for students of all ages and educational levels. Our platform provides learning support for a wide range of subjects, including STEM, Social Sciences, and Languages and also helps students to successfully master various tests and exams worldwide, such as GCSE, A Level, SAT, ACT, Abitur, and more. We offer an extensive library of learning materials, including interactive flashcards, comprehensive textbook solutions, and detailed explanations. The cutting-edge technology and tools we provide help students create their own learning materials. StudySmarter’s content is not only expert-verified but also regularly updated to ensure accuracy and relevance.

##### StudySmarter Editorial Team

Team Engineering Teachers

• Checked by StudySmarter Editorial Team