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,…
We often need to find cell references to use in our formulas. In this post, we will look at several such functions: ADDRESS, ROW, COLUMN, OFFSET, and INDIRECT. We mention INDEX(MATCH) but cover them in other posts. For simplicity, in this post, we use the “A1” format of cell references throughout. The other option, the…
When you want to make sense of a lot of numbers, one of the ways to do it is to get an average (also called the mean). To calculate an average, add all the numbers and then divide the total by the number of numbers. If you added five numbers, you divide the sum by…
CELL returns information about a specific cell. INFO returns information about the operating environment. The various IS- functions at the end of the post are also of interest. CELL(info_type, reference) CELL returns information about the formatting, location, or contents of the reference cell. (It uses the upper-left cell if the reference is a multi-cell range). …
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…
Do you need totals, an average, or a count of cells in a range, but only under certain circumstances? Then one of the several statistical “IF” functions will be useful. They let you use a criterion, or several criteria, to include or ignore chosen cells. They are:COUNTIF, COUNTIFS count those cells in a range that…
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],…