The Excel LINEST function is normally used to fit a straight line to data points. However, using this little-known technique you can also fit higher-order curves. This example shows how to use LINEST to fit Quadratic and Cubic Curves to data. This example comes from the sample spreadsheet LINEST-2-3.xlsx in our Excel for Engineers online…
Do you ever need to convert between different measurement systems? It’s more of a problem in the US, where the last Imperialists stubbornly cling to archaic units in the face of the rest of the world metricating. They inflict units like ounces, gallons, acres, feet and inches on us. What are they talking about? Never…
You say, “I could never use INDEX(MATCH): It looks too complicated!” Well, here is the very simplest example, which anyone can master (or mistress). Here we are only looking for an exact match. For simplicity, we will use one-column arrays only. Once you are happy with this, if you want two-dimensional examples, read this following…
Before Excel 2003, you could get a complete list of Visual Basic Functions by looking in Visual Basic Help > Contents > Functions. In Excel 2010, entering “Functions” into the Help Search box produces nothing useful. Using a specific category, e.g. “Math Functions”, may be more productive. Please be aware of what references to “Excel…
Here is a function that displays the formula in another cell: FORMULATEXT(reference). The argument, “reference”, is the cell address that contains the formula you want to display. This is most useful for documentation purposes. For example, when you want to print out a spreadsheet with its formulas. If you have spent much time on this…
We know about the MAX function in Excel, to get the biggest value in a range of values. But did you know that there is a function to give you the second biggest, third biggest, and so on? It’s the LARGE function. It can also give you the “first biggest”, replacing MAX. Syntax: LARGE(array, k)…
The adjective “Rank” means “having a foul or offensive smell.” (As in “the rank air of death after the battle”) and “very bad” (as in “the rank incompetence of the South African Post Office”). But Excel uses it as a noun, in the sense of “the position in an ordered list”. The RANK function returns…
We have various ways of solving simultaneous linear equations. In this post, we will look at a couple of other methods, and then how we can do it with matrices in Excel. We first consider two variables, and then more. Let’s be clear before we start: What are linear equations? They are of the form…
It is a sad fact of life that, as an engineer, you cannot escape accountancy. Finance gets in everywhere. Hence, you need to have a grasp, not only of engineering functions, but also of some financial functions –like depreciation. In this article, we will look at depreciation. You may be buying a new piece of…
In the previous post, we introduced array formulas. These are formulas you enter into a range of cells using Shift+Ctrl+Enter. That block, or array, works as a unit. You can extend the array easily or, with some difficulty, reduce it. We looked at some array formulas: TRANSPOSE, COLUMN, SUM, and LINEST. If you didn’t read…
One of the most obscure things about Excel has to be array formulas. Weird as snake’s suspenders, they are. We are familiar with the idea that a single formula in Excel occupies a single cell. If you want that formula to be in many cells, you simply copy it to those cells. It will adjust…
Do you ever need to convert units, increase or decrease a range of data, or change its sign? Then this technique should work for you. Excel’s Copy > Paste Special > Add / Subtract / Multiply / Divide lets you transform data in place. You don’t need an intermediate column or row. Say, for example,…