Say that you set up a spreadsheet for general use. You expect the user to type text into certain cells, which you then use in formulas. But they leave some cells blank. If your text formulas refer to those cells, they read as zero, which messes up your text. Use =T(cell) to return an empty…
Microsoft Excel stores dates as sequential serial numbers for use in calculations. 1 January 1900 is serial number 1. 2 January 1900 is serial number 2, and so on. Thus, 1 January 2000 is serial number 36526 because it is 36,525 days after 1 January 1900. Logically, a time of day is a fraction of…
Microsoft Excel stores dates as sequential serial numbers for use in calculations. 1 January 1900 is serial number 1. 2 January 1900 is serial number 2, and so on. Thus, 1 January 2000 is serial number 36526 because it is 36,525 days after 1 January 1900. This means that you can do maths with dates. …
In an earlier post, we used the SUMIFS function to create a summary table like the one on the right of this example: There, we summed the budget figures for various projects, segmenting them by status of Red, Amber, and Green (columns), and by Area (rows). We can create a similar table using a Pivot…
Indecisive? Have difficulty making decisions? You need the IF function to do it for you! Seriously, though, the IF function can take a lot of the drudgery out of your formulas by applying simple yes/no rules. For example, say you’re calculating the velocity of flow in a water pipeline. In line with good practice, we…
Why would I tell you about Excel Functions you will never need? Well, you might be using them now, and not be aware that there are simpler, more elegant alternatives. Are you using any of these: CONCATENATE, CONCAT, PRODUCT, POWER, DAYS, or DAYS360? Then read on… CONCATENATE and CONCAT to Join Strings Syntax: CONCATENATE(text1, [text2],…
In some cases, VBA does not have a specific function, but the Excel Worksheet does. This Excel Visual Basic (VBA) code example demonstrates: How to call that Worksheet Function from a Macro. In this particular example, how to call the Worksheet Function =PROPER() from VBA. The function to downcase parts of surnames that normally appear…
When you want to look up data in Excel, you can choose from various functions: LOOKUP (in two forms), VLOOKUP, HLOOKUP, and the combination of INDEX(MATCH). Which should you use? Here’s a flowchart to help guide you: The first and most important question is, “do you want an exact match or an approximate match?” If…
We often need to find a value between or near two other known values. If we do not have a more accurate curve, a straight line will have to do. This is Linear Interpolation (or extrapolation). Excel is a good way to do it. For example, I read our electricity meters weekly. This lets me…
Naming your ranges in Excel has many benefits, not only for engineers but for everyone. But what is a Range Name? How do I name a range? How do I use it? What is a Range? Let’s start with ranges. What is a “Range” in Excel? It’s just a group of cells. You’ve been using…
Do you need to pick out a name from a list based on some data? It might be the best-performing employee or department. Or the machine next in need of a service. Or the supplier whose concrete mix was the most consistent, or many others. This isn’t specifically an engineering application, but of general use:…