Microsoft Excel 2013 Basics: Creating Simple Formulas
Using other common functions
With over 400 functions in Excel, you may be wondering "how do you get a handle on them, how do you know what to look for? We're going to give you a few examples on this functions worksheet, which is found in the Workbook 03 Creating Formulas and Functions. When you do have that extra moment, if you go to the Formulas tab in the ribbon, recognize that there are some groupings of functions here. Now, depending upon what it is you're doing, maybe you'll click on Math & Trig and begin to see some of the many functions here. Most of us don't have the time to do this, maybe not even the inclination, but if you do pause and hover over one of these, you do get a description of them.
Sure enough, some of these names are going to jump out at you, something about degrees maybe. Converts radiance to degrees that may or may not be useful to you. Here's something called Fact, which opens us to factorials which is something you may have heard of. Logarithms those are builtin there. It sounds like maybe we're in some big fishing expedition maybe, but, based on the work that you do, maybe you do work with financial functions or financial data, and here's a financial group over here. Sure enough, some of these are likely to have relevance to what you do.
Here's a nice one here for calculating mortgage payments, that sort of thing. There are tons of functions. You just don't always know where to begin. Date & Time functions can be pretty interesting for a lot of Excel users as well. There's one in here for picking out the day of the week called Weekday. There's another one for calculating monthly differences, differences between months or so many months out. There's an EDATE reference right there, so many months before or after; there's an EOMONTH. This just goes on and on and on. Let's talk about a couple of functions that you might not have heard about, you wouldn't necessarily need, but on the other hand, I think they have a lot of relevance to certain kinds of lists.
Here's a simple one here. We simply want to count how many names are in column A. It might be a huge list, it might not be. We can count the number of cells that have data in them. That function is called Count A. Now, anytime you type equal and then a letter, you'd see all the functions that begin with that name. Now, I could certainly type this faster than finding it, but nevertheless, if you do find it, for example, "Count"we see it in thereCount A right here, this particular function counts the number of cells on a range that are not empty.
Rather than typing it, we can press Tab right now and use that. Where are we looking? Maybe we're looking in Column A. By the way, it will ignore the icon, the image that's in cell A1, sitting on top of cell A1. People ignore that. If we want to know how many cells have data here, we are counting cell A2 as well. If the question is, "do we want to count how many people work here?" Well, we'd want to subtract 1 from this, but if it's simply a count of how many cells have data, there is our answer, it's 13. If we wanted to change that wording to the right, let's say number of people, then we'd subtract 1 here.
If we want to count the cells that have numbers, how many different salaries do we have here, it's almost the same, but it's called Count. Here, we'll just click Column E. By the way, referring to an entire column often makes sense. If there's nothing else in this columnnow there is text in cell E2, but there's nothing else thereand we're trying to count the number cellswe should get sensible answer here there are 12 of them. In this list here, we're dragging downward to see that. By the way, when you drag downward, you will see in the upper left corner to the left of the formula bar, the indicator up there, and you don't see it at the momentbut as I highlight this again, keep an eye on this areathe name box, to the left of the formula bar here, will indicate how many cells I'm highlighting, and it does it in the following style that says, "12Rx1C"meaning 12 rows by one column.
There are 12 cells here that have numbers in them and here's the formula again. Using the Count function, count the number of cells in Column E that have numbers in them. Now, Median is a commonly used number when dealing with certain kinds of entries. Earlier, you might have seen how on the Formulas tab, you can use the drop arrow for AutoSum to get to a Max or a Min, but we don't see Median in here, so it might be faster in this case to actually type in the name of that function. How do you know there's a Median function? You happened to discover it.
It's probably a good guess, because that's a commonly used statistical measure. The Median salary, say for the New Salaries in column F, What's the Median Salary? There it is right there. Is it different than the average? Well, we could calculate the average, just as easily, and let me move this down actually. Let's do an average as well right here. This time we'll use the AutoSum drop arrow, choose Average, and we don't want that average, but we want the average of column F, and Enter. Of course it will make sense to format both of these and compare them.
Go to the Home Tab. Click the comma button, good enough for now, the Median and also the Average. Now, finding this 2nd Largest entry, it may or may not be that importantit wasn't to me until a few years ago when I needed it a couple of timesand I thought you'd do something like a "max" or "sort the data", but there is a function way, and it's called Large. I don't think you'd exactly guess that. Large says where are we looking? We're looking in column F, comma. The 2nd highest, put in the two, third highest, put in the three, and so on.
What's the second highest salary there? 76,728. It doesn't tell us where it is, but it tells us that it exist in here. Here's an 81,000, so that's the highest. It looks like somewhere in there, we've got a 76,000, there it is right there, that's the second highest. Now, if you're familiar with statistics, you would know about standard deviation. Now, do you make a guess here? Do you start typing? Maybe. Perhaps better go to the Formulas Tab. Strange enough, you don't see statistical here at first, but if you go to More functions, you do see statistical and quite a few entries here.
You could probably guessand guess correctlyif it's going to be standard deviation it begins with the letter "S", probably ST. As it turns out, there's a cluster of them here, and you just have to figure out which one suits your needs best. For those of you who have used this, you knowyou have some sense of what it does it indicates how much variance there is between the average and each of the entries here. It does it in a waited kind of way. Maybe it's this one, maybe it's this one. You'll know which one to use. That example here, it actually will prompt you into highlighting the data, for example, column F, and we'll click OK and get our answer.
Now, if you're familiar with standard deviation and how that works, that's a meaningful value. It's hard to come up with a master list of the functions that you need or that I need or the next person needs, because so many different people use Excel in so many different ways; but the Formulas Tab, at least opens you up to some of the major categories, gives you some ideas and as you work with these more and more, you'll have a better sense of which of these functions is likely to be used. There's just a ton of them. Don't get overwhelmed, but there's no question, they're going to save you a great deal of time as you work with Excel.
Using other common functions provides you with indepth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training
 Subjects:
 Business Education + Elearning
 Software:
 Excel
 Author:
 Dennis Taylor

1m 6s

 Welcome

43s


29m 37s

 What is Excel used for?

1m 49s

 Using the menu system

4m 30s

 The Quick Access Toolbar

4m 41s

 Using the Formula bar

1m 43s

 Using the Status bar

2m 24s

 Using the builtin help

2m 54s

 Creating new files

2m 11s


24m 1s

 Working with dates and times

3m 32s

 Using Undo and Redo

4m 50s

 Adding comments

2m 55s

 Using Save or Save As

3m 57s

30m 7s

 Using SUM and AVERAGE

3m 25s

46m 7s

 Designing borders

3m 26s

 Formatting numbers and dates

4m 31s

 Conditional formatting

4m 21s

 Creating and using tables

9m 59s

20m 40s

17m 51s

30m 30s

 Creating charts

4m 36s

 Exploring chart types

7m 47s

 Formatting charts

5m 42s


12m 49s

23m 0s

20m 25s

23m 50s

 Protecting workbooks

2m 40s

 Sharing workbooks

4m 7s

 Tracking changes

4m 32s

28m 32s

 Sorting data

6m 9s

 Using filters

6m 16s

 Removing duplicate records

2m 38s


35m 2s

 Creating PivotTables

8m 36s

 Manipulating PivotTable data

9m 47s

 Grouping by other factors

2m 33s

 Using PivotCharts

3m 59s


23m 29s

 Using Goal Seek

6m 8s

 Using Solver

6m 34s

 Using Scenario Manager

6m 11s

 Using Data Tables

4m 36s


24m 31s

 Definition and examples

6m 48s

 Creating a simple macro

7m 0s

 Running a macro

10m 43s


29s

 Next steps

29s

Related Courses

Excel 2010 Essential Training5,995,580 Views

Word 2013 Essential Training1,676,538 Views

Access 2013 Essential Training2,699,756 Views

PowerPoint 2013 Essential Training1,662,785 Views

Excel 2013: Pivot Tables in Depth1,327,941 Views

Access 2013 Essential Training2,699,756 Views

Word 2013 Essential Training1,676,538 Views

Excel 2010 Essential Training5,995,580 Views

PowerPoint 2013 Essential Training1,662,785 Views

Foundations of Programming: Fundamentals7,707,894 Views