spreadsheet formulas

 

Four Favorite Spreadsheet Functions

If you've already seen my post, Six Tips for Sexier Spreadsheets, I don't want you to get the idea that snazzy looks is all that matters. That's spreadsheet blasphemy! In order to maintain balance, I want to overview a few spreadsheet functions I find myself using frequently when building spreadsheets. I'm using Google Sheets for these examples, but it should get you on the right track even if you're using a different app.

These functions aren't secrets by any means, and in fact, can be referenced in the complete list of functions for Google Sheets. Don't be intimidated by the list. It's a bit lengthy, but it's well-organized, and it's invaluable if you'll be spending any time in a spreadsheet.

If you're just getting started with formulas, it goes like this: select a cell, type an equal sign (=), and then type your formula. You can get more info for getting started here.

The functions, already!

 

1. Math functions

Ok, =mathfunctions is not a formula. I'm talking about all the math-type functions available in a spreadsheet. Please remember that a spreadsheet is not digital graph paper; its primary function is not to align things in boxes. One of the functions of a spreadsheet is to act as a powerful interconnected calculator. This means you shouldn't be doing any manual math in a spreadsheet.

How do I make my spreadsheet do math for me?

There's a couple ways. The simplest way is to type an equation into a cell, such as =4+7. Obviously, this is more useful if you have large numbers you need to calculate, like =18152809942589/321400000 (the current national debt / the current U.S. population). When you press enter or select another cell, you can see the depressing result.

But the math fun comes from referencing values from other cells. For example, if you had the value of the current national debt in cell A1 and the current U.S. population in cell A2, you could create this formula in cell A3: =A1/A2, which will then display the same depressing result. The benefit of referencing cells like this is that cells A1 and A2 could be calculated from other sources (cells), which could be updated automatically. This, in turn, would create a chain reaction of calculations that are done automatically, and without the chance of human error.

math formulas

 

2. sheetname!

Speaking of referencing other cells, the exclamation mark (!) enables us to reference other cells in other sheets. Please note: this does not reference other documents (though that is also possible), only other sheets in the same document. This comes in really handy for building spreadsheet interfaces, like a summary page of a budget, or an overview of monthly company statistics. You can dump all the data into a raw data sheet, and then reference that data for use in your "interface" sheet. Then, when your raw data is updated, voila, your "interface" sheet updates automatically.

How do I reference cells in another sheet?

It's pretty simple, actually. The syntax is SheetName!A1. For example, =sum(Sheet2!A1,Sheet2!A2) references cells A1 and A2 in Sheet2. Another example could be =RawData!A1*A3/RawData!B1, which multiplies the values of cells A1 and A3 in the sheet named RawData, and then divides that by the value in cell B1 of the same sheet. That's all there is to it! Now you'll be working across multiple sheets in no time.

remote sheet

 

3. ArrayFormula()

You might already know about being able to quickly duplicate a formula down a column or across a row. But sometimes that isn't quite enough to do the job. For example, let's say you have a sheet with three columns: Cost, Quantity, and Total. You want values in the Cost and Quantity columns to be multiplied and the product to be displayed in the Total column. That would be easy enough, but you use a Google Form to add rows of data to this sheet (yes, you can do that, and it's wonderful!), and you need the calculations to automatically work when the new rows are added. This is where ArrayFormula comes in. This allows you to project a formula into a range of cells. In other words, you can instruct the spreadsheet to project this formula into these other cells. This starts to get a little complicated, but it's worth digging into and making this work for you.

How do I array a formula?

In its simplest form, it only requires that =arrayformula(cellrange*cellrange) be put in the top-most cell where you want the results to be displayed. In this example, we're using multiplication (*), but arrayformula can use any math function, or can be used in combination with other formulas.

As you can see, however, the result lacks eloquence, specifically that it displays zeros for the entire length of the column when there is no data available (because nothing plus nothing equals zero). What we want to do is have the spreadsheet do the calculation only if the cells in either of the first two columns have a value in them. For this, we'll be using two functions: the If function, and the does not equal function, which is <>.

First, the If function (by the way, using capital or lowercase letters does not matter in formulas). The If function basically works like this: If(this is true)(then do this)(otherwise, do this). For example: =if(A1+B1=20,(A1+B1),"Not 20") is instructing if A1 + B1 equals 20, then add A1 and B1 (and display the result), but if it does not equal 20, display the text "Not 20"

Now let's look at the does not equal (<>) function. In our case, we're trying to determine if the cells in column A or column B are empty. It might sound confusing, but the easiest way to do that is to ask if those cells do not equal "nothing." This isn't English class, so we're allowed to use a double negative. If a cell does not equal nothing, then there is something in that cell, in which case we're good to go. In this case, the way we denote nothing is by using empty quotations (""). Now put them together. We want to determine is a cell does not equal (<>) nothing (""). Let's put this all together.

Using the formula =arrayformula(A2:A*B2:B), let's add the if and does not equal functions. Remember the three sections of the if function: if(this is true)(then do this)(otherwise, do this). Let's do it section-by-section.

 

=arrayformula(if(A2:A<>"",

First, the arrayformula is instructing the spreadsheet to use this formula for every cell in the whole column. The rest is saying if the cells A2:A do not equal nothing, then... (which is why we need to add more to the formula). 

 

(A2:A*B2:B),

We had this part earlier. This is the calculation we want it to perform if the first section is true.

 

""))

The content inside the quotations (nothing) is what we want to display if the first section is not true. If either of the cells in column A or column B are not not nothing (if it's empty), we want to display nothing in the cell in column C. Finally, the )) are simply to close out the if and arrayformula functions of the formula. Every time you have an open parenthesis, you need a close parenthesis. 

At last, our complete formula is:

=arrayformula(if(A2:A<>"",(A2:A*B2:B),""))

It means use the following formula for every cell in this column: if the cells A2:A do not equal nothing, then multiply the cells in A2:A by the cells in B2:B, otherwise, don't display anything.

If you're not familiar with arrays or multi-part formulas, this might seem confusing and overwhelming, but don't give up! Try again later, or ask for some help. Once you understand what is happening, you'll find it to look scarier than it is.

Good news: we end on an easy one!

 

RandBetween()

This function returns a random number between two values. That's it! The syntax is just as simple: =randbetween(1,100) returns a random value between, you guessed it, 1 and 100. You might be wondering why this function is on my shortlist of functions. Well, you might want to assign random numbers as serial numbers to an inventory or employees (you would also need to make sure the number isn't already in use). Maybe you're experimenting with some fun new functions you found on a blog, and you need some "dummy" data to test with. Maybe you need random numbers for a game, or a test, or a simulation. There's countless cases where random numbers can be useful, and this is the function that will save the day.

An extra tip about randbetween: if you're using it for "dummy" data, and you don't want it to keep changing (certain spreadsheet actions will cause formulas to recalculate, and this will produce new random numbers), select all the cells with random numbers in them, copy, and then paste special > values only. This will paste the numbers back into the cells, but the randbetween formula won't be there anymore.

randbetween formula

 

What's your favorite function?

That's it. Hopefully, that's enough to get you exploring with formulas a bit more, and maybe give a boost in functionality to your spreadsheets. Do you have a favorite spreadsheet function? I'd love to hear about it!

Did you enjoy this post?
"Four Favorite Spreadsheet Functions" - By Darrell Haemer - - No Comments

ADD COMMENT