In this post, you'll learn how to perform constrained optimization in Excel through an example where we will maximize the flow rate in an open channel. The example will show that there is an optimal relationship between the channel dimensions that maximizes the flow rate for any required … Read more about Constrained Optimization in Excel โ Maximize Open Channel Flow

## Using Excel Solver for Linear Regression

The Excel Solver can be used to perform a least squares regression. For most situations, you can do regression using either trendlines in the chart or the LINEST function. However, there may be situations where you want to find a best fit manually. Even if you never need to do a manual fit, this … Read more about Using Excel Solver for Linear Regression

## Unit Aware Calculations in Excel

Every engineer knows that for the results of engineering calculations to be correct, the units of the input values must be correct. In the case of a calculation like F=ma, the units must be consistent with each other (e.g. N, kg, m/s^2) and in the case of a unit-specific calculation like P=Tn/5252, … Read more about Unit Aware Calculations in Excel

## Using Excel Form Controls to Control Spreadsheet Inputs

Excel provides another way to ensure input values meet certain criteria other than data validation: form controls like combo boxes, list boxes, and radio buttons. To add form controls, we have to enable the Developer tab, because it's not turned on by default when you install Excel. Go to File > … Read more about Using Excel Form Controls to Control Spreadsheet Inputs

## Using Excel Data Validation to Avoid Errors

Excel's data validation tool allows you to constrain a cell's inputs to meet certain criteria. This can help prevent errors from occurring. In the example in the previous section, we can prevent the divide by zero error by forcing the diameter of the steel to always be less than the diameter of the … Read more about Using Excel Data Validation to Avoid Errors

## Rounding Functions in Excel

Excel has several functions for rounding numbers. Function Description INT Rounds down to the nearest integer ROUND Rounds the number to a specified number of digits ROUNDUP Rounds a number up, away from zero ROUNDDOWN Rounds a number down, toward … Read more about Rounding Functions in Excel

## Using Cell Names in Excel

As previously mentioned, one of the biggest complaints that engineers have against Excel is that it's difficult to understand formulas in a spreadsheet. Formulas refer to cell references like C6, D7, etc. However, Excel has a feature called Named Ranges that allows you to assign names to … Read more about Using Cell Names in Excel

## Troubleshooting Engineering Formulas in Excel

Excel has built-in tools that are very useful for troubleshooting formulas, even if the formulas are complex. The example worksheet will calculate the force in the components of a structure made of a steel bar encased in concrete. There is a uniform load applied across the top. This kind … Read more about Troubleshooting Engineering Formulas in Excel