ALL POSTS BY: Doug Midkiff
Excel Tip of the Week: Error Codes
Today’s topic is Excel formula Error Codes.
Not long after learning how to create formulas in Excel, you probably started to see the ever-so-annoying formula error codes. This post will hopefully demystify these error codes and give you some techniques on how to deal with them.
There are 8 formula error codes in Excel. I’ve created a table below with their definitions and common troubleshooting techniques. Some of the troubleshooting actions are pretty obvious, but I included them for completeness sake.
| Error Code | Definition | Possible Fix |
|---|---|---|
| ####### | Column is not wide enough to display all the characters in a cell, or a cell contains negative date or time values. | Widen the column or fix the date error. |
| #DIV/0! | A number is divided either by zero (0) or by a cell that contains no value. | Don’t divide by zero. |
| #N/A | A value is not available to a function or formula. | Check your formula carefully for misplaced commas or missing information. |
| #NAME? | Excel does not recognize text in a formula. | Make sure your formula or named range is spelled correctly. |
Owen. Wired.
When you get tired of going to the Starbucks across the street, OwenBloggers has you covered.
Sam & Zoe’s Coffee House and Café
Website: http://www.samandzoes.com/
Address:
Hours: Mon-Fri – 6:30a-8p; Sat – 7a-7p; Sun – 8a-7p
Seating: One of the smaller shops in Nashville, but good amount of tables given the size.
Outdoor Seating: Yes. Covered.
Selected Prices: Small/Large Coffee: $1.50/$2.05, Medium Latte: $3.05
Outlet availability: Decent. You gotta stick to the back wall.
Wifi: Sort of. They turn off the wifi from 10:30am-2pm. Sneaky way to keep the limited table space open during lunch time. I was able to pick up wifi from the gas station next door, but it kept going in and out.
Driving Distance from Owen: 4.3 Miles
Parking (Car): Yes, but limited.
Parking (Bike): No.
Food: Full breakfast and lunch menu. They get bagels from Star Bagel. They have breakfast burritos, oatmeal, granola, sandwiches, burgers, salads, etc.
Scene: Located close to quite a bit of residential so it has a neighborhood feel with a very diverse crowd.
Other Notes: The only indie coffee shop in Nashville with a drive-through window. Claims to be the second coffee house in Nashville.
The Case for Case Competitions
In my time at Owen I’ve had the opportunity to compete in 4 case competitions. I would encourage all students to take part in at least one case competition. The depth of learning and the experience gained are completely worth the effort. The competitions I’ve done have all been real estate-related, but I’m sure that my takeaways hold true for just about any competition.
Deep Learning – Most cases in business school, with a few exceptions, are read, analyzed, discussed, and put to bed in just a few hours. It might have taken your group a week to write that paper, but how many hours during that week did you actually spend thinking about that case? Two? Maybe Three?
A few weeks ago I spent just about every waking hour during a 72 hour span dissecting, discussing, and analyzing a real estate development case (and there weren’t very many ‘sleeping hours’ during that time) Nowhere else in business school I have spent as much time on a single project. It forced me to explore concepts and learn new things about real estate development which I wouldn’t have learned anywhere else at Owen.
Excel Tip of the Week: Check Box
Today’s topic is how to create a check box.
Check boxes are technically an advanced Excel feature and somewhat hidden in Excel. But once you know how to create one, it is relatively simple to use.
Before we jump into creating check boxes, the first step is to enable the “Developer” tab in the Excel ribbon. By default this tab is hidden, and it is where you’ll find the check boxes among many other useful advanced Excel features. Here is how to enable the Developer Tab:
Windows:
- Click the File tab.
- Click Options.
- In the categories pane, click Customize Ribbon.
- In the list of main tabs, select Developer.
- Click OK to close the Options dialog box.
Mac:
- Click on the ‘Excel’ menu in the menu bar
- Click on Preferences
- Click on the Ribbon icon
- Select the Developer Tab
- Click OK
Check boxes give you the ability to toggle between two scenarios. For example, I’ve used them to toggle between a simple or detailed assumption. Or you could use it to turn things on and off. Let’s look at an example. First download this spreadsheet.
Excel Tip of the Week: Pivot Tables
This week’s topic is pivot tables.
Pivot tables are for organizing and summarizing large amounts of data in a quick and easy fashion. Anything you do with a pivot table can also be accomplished with formulas. However, pivot tables have a couple advantages over using formulas. First, it is quicker. Pivot tables allow users to drag-and-drop their information into a summary table without having to know a single formula. Second, pivot tables allow for greater flexibility. If your data is not arranged to your liking, then a few clicks of the mouse can completely rearrange the data. This is much more flexible than rewriting several formulas.
Let’s walk through an example that highlights some of the most basic pivot table features. Please download the example spreadsheet here.
When you open the spreadsheet you’ll notice that there is a large amount of data arranged in columns with column headers. This is how data needs to be arranged before using it in a pivot table. The information in the example spreadsheet is a list of over 2000 “green” cars along with information about those cars such as: fuel type, number of cylinders, air pollution score, etc. Follow these steps to create a pivot table for this data: