This is the second post in a series of articles about learning the great functions in spreadsheet tools like Microsoft Excel and Google Spreadsheets. You can read Part 1 here.
Note: All of these functions will work with Microsoft Excel and Google Docs, though their syntax may vary slightly. All my examples will be done in google docs.
You can follow along with all the examples from the Sample Google Spreadsheet Here.
Our Sample Doc will be similar to the one used in Part 1, but we’ll be building things to be a bit more dynamic. Values will start to use lookup tables, and more advanced functions that will be covered in this doc. Our Virtual Crops will start to reflect the values needed for any kind of invest/express type game, which should segue nicely into the next series I’d like to cover on economy design. Please keep in mind that these values are not tuned/balanced in any kind of way, so focus on the functions only! The next series of posts will cover building actual tuned values.
Here’s what we’re going to cover in this post:
- Data Validation
I had originally planned to cover a bunch of other functions, but after writing out the first 2, this post was already becoming a monster in size. Looks like a Part 3 of this series is definitely going to happen. After speaking with other people who have read this, it’s likely it’ll extend to a Part 4.
Data Validation is not exactly a function, but is definitely something worth knowing. It allows you to set specific rules and criteria for the kind of data that can appear in a cell, and also lets you create drop down lists of data. These are great for ensuring that data added to your spreadsheets adhere to specific rules. If someone takes over maintaining your spreadsheet in the future, they’ll have a much easier time if your tuning and information data is easy to work with.
In our example sheet, we’re going to create a list of maturity times that we’ll use to populate into a dropdown list in a cell. Trust me when I say this sounds more complicated than it really is. I’ve created a sheet called ‘Lookup Tables’ which we’ll use for the reference data.
Here’s the first lookup table I’m adding. It’s a list of literal times in english text (which we’ll populate in a dropdown list) and their actual time in seconds, which we’ll use a vlookup to populate in the next section.
So lets get this into a nice looking dropdown list!
In our ‘Crops’ sheet, I’ve created a new column called ‘Maturity Time’, which is where we’ll make it easy to determine the value for a specific crop. Right click the empty maturity time cell for Strawberries (B7) and select Data Validation from the bottom.
Our Cell Range will remain the same (Crops!B7), but it’s the criteria we’re going to modify. Click on the ‘number’ list and select ‘Items from a list’. From here you can put in a comma separated list, and more importantly, you can define a range of cells to use, which is what we’re going to do.
Click the select data range button. Once you get the ‘What Data?’ popup, click on the ‘Lookup Tables’ tab. Select the full range of cells with literal time values. You should see the following value in the field:
Click OK, then click save. We now have a dropdown list!
Be sure to click the drop down box arrow to pick the values you want.
TIP: If you want to use this dropdown list for the rest of the cells, just use the format painter (the icon to the left) to copy the format to the rest of the crops.
So, we’ve got cool dropdowns populating now, but if our database only takes values in seconds, we’re going to need to populate a field that it can read. Luckily for us, we have a function called Vlookup that can do this for us!
I’ll be the first to admit that until I really had some experience working with this function, I didn’t really understand it. Hopefully I can do an acceptable job of explaining why you will want to know it, and how to wield its power!
What Vlookup will allow us to do, is automatically populate a field, based on the value of another field.
Meaning, when we pick a maturity time from the dropdown list we just created, the maturity time in second’s field will automatically populate with the value in seconds. In order to do this, it will look up the value of the maturity time on the ‘Lookup Tables’ sheet, and then pull the value of the corresponding Cell next to it.
So in the case of our Strawberry, when we pick 5 Minutes as our Maturity Time:
The Maturity Time in Seconds field is automatically populated with the exact value of 300 seconds. It does this, because of the table that we bult on the ‘Lookup Tables’ sheet, and using a Vlookup.
Still with me? Good. Let’s look at the syntax for the function:
VLOOKUP(search_criterion, array, index, sort_order)
A bit scary right? It’s ok, let’s break down each value and build the vlookup for strawberry maturity time:
The search_criterion is just the value we want to lookup somewhere else. In this case, it’s the literal maturity time that is on the crop sheet. We’ll search for this value on the ‘Lookup Table’ sheet.
Currently our function looks like this: “=VLOOKUP(B7,” – It’s going to search somewhere for the same value as what is in the cell B7.
The array is the range of the cells that contain both the value we’re searching for, and the lookup value we want to return. If there are multiple columns between the column that will contain the search criteria, and the lookup value we want to return, specify the full range of columns in the range.
Our function now looks like this: “=VLOOKUP(B7,’Lookup Tables’!A:B“ - It’s going to look for the value of B7 on the ‘Lookup Tables’ sheet, in columns A and B.
You can type these values in by hand, or click them individually while separating them by a comma from google docs.
The index is the column number in the range we provided that contains the value we want to return. All ranges specified start at 1, and increase. So in our example, Column A is 1 and Column B is 2. We want to return the value of Column B (300).
Our function now looks like this: “=VLOOKUP(B7,’Lookup Tables’!A:B,2“ - It’s going to look for the value of B7 on the ‘Lookup Tables’ sheet, in columns A and B, and then return the corresponding value of the cell next to the match. So whatever is in the cell to the right of ‘5 Minutes’ will be returned.
The sort_order paramater is only necessary if you’re trying to find the closest match to the value you provided, specifically in a numerically sorted list. Because we know the match will be exact, we don’t need to worry about this. Our sort_order will just be set to false.
Our Complete function now looks like this:
If we copy this formula to the rest of the blank maturity time (s) values, and put in appropriate times for the literal values in Column B, you’ll see that their maturity times populate with the proper lookup data!