Chapter 7. Finding Functions: Mine Excel’s features on your own
Excel has more functions than you will ever use.
Over many years and many versions, the program has accumulated specialized functions that are terribly important to the small group of people who use them. That’s not a problem for you. But what is a problem for you is the group of functions that you don’t know but that are useful in your work. Which functions are we talking about? Only you can know for sure, and you’re about to learn some tips and techniques to finding quickly the formulas you need to get your work done efficiently.
Should you rent additional parking?
You’re in charge of the Dataville Convention Center parking program. They do a big entertainment business in Dataville, but they have a problem. If they are expecting more than 1,000 ticket buyers to attend an event, they need to rent more parking spaces.
In the upcoming month, are they expecting more than 1,000 attendees for any of their events? Your challenge is to find out using their ticket sales data, and you’ll get free box seats to your favorite shows if you can set up a workflow.
You need formulas to count tickets sold for each day in this month’s weekends.
Function Review
Here are all the functions you’ve learned so far. What do they do?
SUM | Tells you which in a range of numbers is smallest. |
MIN | Tells you whether a cell is of data type text. |
AVERAGE | Converts text into an integer that represents a date. |
VALUE | Gives you the mean of a range of numbers. |
ISTEXT | Returns the standard deviation of a range. |
STDDEV | Converts text to numbers. |
DATEVALUE | Adds numbers together. |
DATEDIF | Returns the difference between dates using a metric you specify. |
You need a plan to find more functions
Finding and learning new functions in Excel is one of the core skills you need to develop. Excel has hundreds of functions, and it would take forever for you to read through all of them every time you wanted to learn a new formula.
Excel’s help screens are loaded with tips and tricks
To get help on any of Excel’s scads and scads of formulas, start by clicking the help button on the top right of the Excel screen.
It used to be that you’d never look at the documentation for Excel or any other computer program. No matter whether it was on the printed page or on computer help screens, it was hard to read and poorly written.
Those days are over for Excel. The current generation of help documentation is written to be understood by real, live human beings like you. In fact, it’s so useful that you should dip into the docs occasionally just to explore the new features, not just for when you’re looking for a specific formula.
Here’s a closer look at Excel’s main help page, the page you get when you click on the blue help button. Let’s explore it.
Here’s the convention center’s ticket database for the next month
Each record represents a single ticket sold for a single event on a single date. Your task is to take this ticket data and see which days will have more than 1,000 visitors to the Dataville Convention Center.
This data has only weekend ticket sales, because attendance never comes close to exceeding 1,000 on weekdays. What you need to do is create a list of weekend days for the remainder of the month and then count the number of tickets sold for each of those days.
We need to summarize ticket sales for weekend shows for the remainder of the month. First, let’s create a table that lists the weekend days in this month.
Create a new sheet in your document by clicking the “new sheet” tab. Double-click on your new sheet’s tab, and rename the sheet Summary.
Generate a series of numbers that represent the weekend days of this month. Follow the example below and the instructions in the annotations.
You just endeavored to create a list of numbers representing the remaining weekend days of this month. How’d it go?
Create a new sheet in your document by clicking the “new sheet” tab. Double-click on your new sheet’s tab, and rename the sheet Summary.
Generate a series of numbers that represent the weekend days of this month.
Now let’s make some proper date values out of these numbers.
The problem is, we don’t yet have the formula to do it. Under the help screen, click “Function reference,” then “List of worksheet functions (by category),” then “Date and time functions.”
Take a look at these functions, and click on any that seem promising. Which one do you think you can use to build proper date values out of the day integers you’ve created, and why?
You just inspected all of Excel’s date functions at once. Did you find one that you think will enable you to create dates out of the numbers you entered to represent this month’s weekend days?
The DATE formula is the one we want. If you look at the documentation, you can see that it takes three numeric values as its arguments, and we already have the numbers representing days. The numbers representing month and year are simply 6 and 2010!
Take a closer look at the docs for DATE....
Anatomy of a function reference
Here’s the help window for the DATE function. The documentation for individual functions is really interesting and useful.
Not only can you use the docs to find out what functions do, but you can also use them to learn about functions’ eccentricities—all the different types of arguments they accept and all the sorts of values they return, including explanations for why different errors might result from the same formula.
The Dataville Convention Center COO checks in...
Better get your spreadsheet ready for this last figure and head back to the function reference to find the formula to do this.
Functions are organized by data type and discipline
Now you’re back in the function reference, looking for something that can count tickets for each date. Where to start? It was obvious when you had to build those dates: you just looked in the date category. But there’s no “count ticket sales” category.
Here’s the trick.
When searching for a function, pick a category first by thinking about your problem, and then inspect individual formulas in that category. That way, you’ll avoid scanning hundreds of irrelevant formulas.
Fill in the blanks with the category names from the pool.
I use the ______________ category to calculate how many payments I have left on my mortgage.
The ______________ category would help me extract the first name out of a cell that had first and last names.
If I need to calculate sine and cosine, the ______________ category is the place to go.
Formulas that deal with TRUE/FALSE values are in the ____________ category.
The ________ category is what I need for counting instances of a date.
Note: Each thing from the pool can be used only once!
Fill in the blanks with the category names from the pool.
Note: Each thing from the pool can be used only once!
Your spreadsheet shows ticket counts summarized for each date
The COUNT family of formulas is a really versatile way to analyze repetitive elements in a list. The formulas enable you to get the size of a list, count the numbers in a list, count the blank cells in a list, and count based on multiple criteria.
You’ll almost certainly have use for one or more of these formulas in the future, and when you do, you can just head over to the help docs and use your understanding of them to figure out which formula matches your problem.
From: Dataville Convention Ctr. COO To: Head First Subject: |
Dear Head First, |
Good, but... |
Any chance you could show me only the list of dates that expect more than 1,000 people? |
—COO |
