Introduction

Welcome! SUM, COUNT & Trig Functions Guide

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Excel Expert Level 5. In this course we will cover advanced math and statistics functions in Microsoft Excel, including the use of SUM, creating running balances, and working with SUMIF and SUMIFS for summing data based on specific criteria. We will also discuss various counting and rounding functions, explore mathematical and statistical functions such as median, mode, product, square root, and generating random numbers, and talk about trigonometric functions like tangent. This course is designed for experienced Excel users and is part of a larger series that progresses from beginner to developer levels.

Navigation

Prev: X05
Next: Lesson 01

Keywords

TechHelp Excel, sum function, sumif, sumifs, running balance, counting functions, rounding functions, median, mode, product function, square root, absolute value, random numbers, trigonometric functions, tangent function, sine, cosine, degrees, radians

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Excel 2010 Expert Level 5 brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Today's class is all about math and statistics functions. We'll start out by working more with the sum function. You'll learn how to create a running balance, for example. We'll learn about two very powerful functions, sum if and sum ifs, which allow you to sum up values if they match specific criteria. For example, given a list of orders, show me all of the orders that are past due and unpaid and total those up.

We'll learn about all the different counting and rounding functions. We'll learn about tons of different mathematical functions, median, mode, product, square root, absolute value, generating random numbers and lots more. And we'll see some different trigonometric functions in Excel. I'll show you how to calculate the height of a building using the tangent function.

This class is designed to be used with Excel 2010. However, even if you're using an older version of Excel, most of the material covered today should work just fine. This is an expert level course for Excel 2010. This class was designed for a user who has a good amount of experience with Excel, preferably who has taken all of my beginner classes and the first four expert classes.

My courses are broken up into four different groups: beginner, expert, advanced, and developer. The beginner courses are for novice users with little or no experience with Excel. The expert series, which is what you're watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did, and we'll cover more functions, features, tips, and so on.

When you've mastered the expert classes, move up to the advanced lessons. You'll learn how to build macros, build user forms, create your own templates, and many more advanced features that not everyone will use, but they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer series are designed to teach you how to program in Visual Basic for Applications with Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and to integrate Excel tightly with the other Office applications.

Each of my series is broken down into different levels. For example, the beginner series contained five different levels, which you should have taken previously. This class is the fifth level of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on the lessons learned in the previous levels.

When you finish all the expert classes, move up to the advanced series, and then finally, the developer series.

Now, I'll take a more detailed look at exactly what we're going to learn in today's class.

In lesson one, we're going to learn more with the sum function, we'll learn how to use sum with multiple ranges, and we'll see how to create a running balance.

In lesson two, we're going to learn about the sum if function, which allows you to sum up a list of numbers if specific criteria are met.

In lesson three, we're going to learn about sum ifs, which is related to sum if, but it allows multiple criteria for each of your calculations.

In lesson four, we're going to learn a couple of different functions for counting and rounding numbers.

In lesson five, we're going to take a look at a bunch of different functions that have to do with math and statistics: median, mode, product, power, square root, quotient, mod, ABS, sign, rand, and randbetween. Lots of different stuff in this lesson.

In lesson six, we're going to take a real quick look at trigonometry in Excel. I'll show you the tangent function and how to use it to calculate the height of a building, and we'll talk about the other trig functions like sine and cosine, degrees and radians, and so on.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel Interactive Student forums.

If you're watching this course using my custom video player software, or online on my web theater, you should see the student forum for each lesson appear in a small window next to the class videos, if you have an active internet connection. Here, you'll see all the questions that the other students have asked, as well as my responses to them, and the comments that some of the other students may have made. I encourage you to read through these questions and answers as you start each lesson. Feel free to post your own questions and comments as well.

If you're not watching your lessons online, you can still visit the student forums later by visiting excellearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning, and follow along with my examples. Actually, create the same spreadsheet that I make in the video. Build the spreadsheet with me step by step.

Don't try to apply what you're learning right now to other projects until you've mastered the sample spreadsheet. If you get stuck, or don't understand something, watch the video again from the beginning, or tell me what's wrong in the student forum. Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you'll see that it's really easy to use.

Quiz

Q1. What is the purpose of the SUMIF function in Excel?
A. To sum values that meet specific criteria
B. To count the number of cells in a range
C. To round numbers to the nearest integer
D. To generate random numbers between two values

Q2. What is the difference between SUMIF and SUMIFS functions?
A. SUMIF uses one criterion, SUMIFS can use multiple criteria
B. SUMIFS is only for text values
C. SUMIF can only sum one column at a time
D. SUMIF always sums entire worksheets

Q3. When would you use a running balance in Excel?
A. When you want to total sales for only one month
B. When you want to calculate a cumulative total every row
C. When you want to round numbers to two decimals
D. When you want to count all blank cells

Q4. Which function would you use to return the most frequently occurring value in a list?
A. MODE
B. MEDIAN
C. SUMIF
D. QUOTIENT

Q5. What does the ABS function do in Excel?
A. Returns the absolute value of a number
B. Calculates the average between two cells
C. Generates a random number
D. Counts all cells in a range

Q6. If you want to count the number of items in a range that match a specific criterion, which of the following functions should you use?
A. COUNTIF
B. PRODUCT
C. POWER
D. MOD

Q7. What type of functions are RAND and RANDBETWEEN in Excel?
A. Functions that generate random numbers
B. Functions used for rounding values
C. Functions that convert degrees to radians
D. Functions that find the maximum value

Q8. Which function would you use to calculate the square root of a number in Excel?
A. SQRT
B. SIGN
C. AVERAGE
D. SUM

Q9. What is the purpose of using developer-level skills in Excel?
A. To program in Visual Basic for Applications and automate tasks
B. To create pie charts and bar graphs
C. To merge cells and format text
D. To insert hyperlinks

Q10. The tangent function in Excel can be used to:
A. Calculate the height of a building using trigonometry
B. Sum values based on text criteria
C. Generate random text in a worksheet
D. Find the minimum value in a list

Q11. What is the recommended way to get the most out of the course according to the instructor?
A. First watch each lesson fully, then follow along with the examples step by step
B. Pause frequently and try to build your own projects immediately
C. Only read the manual, skip videos
D. Memorize all formulas before starting

Q12. What should you do if you have questions or do not understand a topic in the lessons?
A. Post your question in the student forum
B. Wait until the next video
C. Search for answers outside the course
D. Skip the topic completely

Answers: 1-A; 2-A; 3-B; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone focuses on advanced math and statistics functions in Excel 2010, designed for those of you who already have significant experience with Excel and have completed the earlier beginner and expert courses.

I will begin by revisiting the sum function and demonstrating how you can work with multiple ranges and set up a running balance. We will move on to discuss two of Excel's most powerful functions: sumif and sumifs. These allow you to add up values in your data based on one or multiple criteria. For example, if you have a table of orders, you can total up only those that are both unpaid and past due.

Next, I will cover the various functions available for counting and rounding numbers. This includes a broad assortment of mathematical and statistical functions such as median, mode, product, power, square root, quotient, mod, absolute value, sign, and functions for generating random numbers like rand and randbetween. I will also introduce you to basic trigonometric functions in Excel, specifically showing how to find the height of a building using the tangent function, and explaining related concepts like degrees, radians, sine, and cosine.

Although this class is tailored for Excel 2010, almost everything I present will also work in earlier versions of Excel.

If you have not already completed my beginner classes and the first four expert levels, I recommend starting with those before proceeding with this course. My curriculum is structured into four main series: beginner, expert, advanced, and developer. The beginner series is for those new to Excel. This expert series, which you are currently watching, is for users who are already comfortable with the software and are ready to explore more in-depth topics and advanced features. After mastering the expert-level content, you can move on to the advanced courses to learn about macros, custom templates, user forms, and other functionalities that greatly expand what you can do in Excel. The developer series focuses on programming in Visual Basic for Applications, giving you the skills to automate tasks and build fully customized solutions.

Each series is divided into levels, with each level building on knowledge from the previous one. This class is the fifth level in the expert series, and each lesson within this class covers different essential topics.

Let me break down what we will be covering in today's class:

In lesson one, we will dive deeper into the sum function, working with multiple ranges and setting up running totals or balances.

Lesson two introduces sumif, showing how to total up values based on a single condition.

Lesson three expands on this with sumifs, allowing you to use multiple criteria in your calculations.

In lesson four, we look at a variety of counting and rounding functions.

Lesson five covers many mathematical and statistical operations in Excel, including calculating the median, mode, product, power, square root, quotient, mod, absolute value, sign, random numbers, and more.

Lesson six gives a brief overview of trigonometric functions, focusing on the tangent function for practical examples like calculating building heights, and touching on related concepts such as sine, cosine, degrees, and radians.

If you have any questions as you go through these lessons, remember you can post them in the interactive student forums on my website. If you are using my custom video player or watching online, the forums for each lesson will appear next to the video. This is a great place to see common questions, my responses, and comments from other students. Even if you are not watching online, you can always visit the forums later at excellearningzone.com/forums.

To get the most out of this course, I recommend that you first watch each lesson all the way through. Afterwards, go back and work along with me, recreating the sample spreadsheets step by step. This hands-on approach will help you truly understand the concepts. Do not rush into applying these techniques to your own projects until you have mastered the sample examples. If you run into trouble, review the lesson again or ask for help in the forums. Excel can seem overwhelming at first, but with practice you will find it much easier to use than it might appear.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Working with the SUM function
Creating a running balance
Using SUM with multiple ranges
Using SUMIF to sum values based on criteria
Using SUMIFS for multiple criteria
Counting functions
Rounding functions
Using MEDIAN function
Using MODE function
Using PRODUCT function
Using POWER function
Calculating square roots
Using QUOTIENT function
Using MOD function
Using ABS function
Using SIGN function
Generating random numbers with RAND
Generating random integers with RANDBETWEEN
Introduction to trigonometric functions
Using TAN function to calculate building height
Working with SIN and COS functions
Converting between degrees and radians

Article

Welcome to an expert level tutorial on math and statistics functions in Microsoft Excel. In this guide, we are going to dive deep into working with sums, running balances, conditional summing, counting, rounding, and a variety of useful statistical and mathematical functions. You will also get a brief look at how Excel can handle trigonometry, including calculating the height of an object with the tangent function. This article aims to help you understand these concepts clearly, providing examples to ensure you can apply everything easily.

Let us start with the SUM function, which totals up a range of numbers. You are probably already familiar with basic use of SUM, but there are some advanced techniques worth knowing. For example, to sum multiple ranges at once, you can use a formula like =SUM(A1:A10, C1:C10). This totals both ranges at the same time. To create a running balance, such as for a checkbook register, you can enter your starting balance in one cell, then in the next row use a formula like =previous_balance_cell + deposit - withdrawal. You can use fill-down to copy this running total formula for each subsequent row, letting Excel keep track of your balance as transactions are entered.

Next, let us explore conditional summing with the SUMIF and SUMIFS functions. SUMIF allows you to sum a range of values that meet a single criteria. For example, if you have a list of sales with customer names in column A and amounts in column B, you can total all sales for a particular customer with =SUMIF(A:A, "Smith", B:B). This adds up all the values in B where the name in the same row of A matches "Smith". If you want to sum values based on multiple conditions, use SUMIFS. For example, if you want to calculate the total for orders that are both past due and unpaid, you could use a formula like =SUMIFS(C:C, A:A, "Past Due", B:B, "Unpaid"), where column C contains order amounts, A contains order status, and B indicates payment status. SUMIFS lets you specify as many pairs of criteria as needed.

Counting and rounding functions are also very handy. COUNT counts how many cells in a range contain numbers. COUNTA counts all non-empty cells. COUNTBLANK tells you how many are empty. If you want to count only numbers that match certain criteria, use COUNTIF or COUNTIFS, which work similarly to SUMIF and SUMIFS. For rounding, the ROUND function lets you round a number to a certain number of digits, like =ROUND(B2, 2) for two decimal places. Use ROUNDUP and ROUNDDOWN when you always want to round up or down, regardless of the value.

Excel includes a lot of useful statistical and mathematical functions that you might not use every day, but can be very powerful. MEDIAN gives you the middle value in a range. MODE returns the most common value. PRODUCT multiplies a range of numbers together. POWER raises a number to a specific exponent, as in =POWER(2,3), which gives you 8. SQRT finds the square root. QUOTIENT performs integer division and returns only the whole number part. MOD gives the remainder after division, which can be helpful for things like determining if a number is even or odd. The ABS function returns the absolute value, ignoring sign. SIGN tells you whether a number is positive, negative, or zero. When you need random numbers, RAND produces a new random decimal between 0 and 1 each time your worksheet recalculates, and RANDBETWEEN gives you a random whole number between two values, such as =RANDBETWEEN(1, 100) for a random number from 1 to 100.

Trigonometric functions are fully supported in Excel, and this comes in handy for scientific or engineering calculations. For example, to calculate the height of a building when you know your horizontal distance from the building and the angle between the ground and your line of sight to the top, you use the TAN function. Suppose your distance from the building is in cell A2 and your angle in degrees is in B2. Since Excel's trigonometric functions use radians, convert degrees to radians with the RADIANS function. The height can be calculated as =A2 * TAN(RADIANS(B2)). This gives you the vertical height based on your distance and angle. Similarly, you can use SIN, COS, and other trig functions, with DEGREE and RADIANS helping you convert units as needed.

As you work through these examples, remember that practice is the best way to reinforce your understanding. Recreate these sample formulas in your own Excel worksheets. Build example spreadsheets, modify the numbers, and see how the results change. If you run into any challenges or something does not work as expected, recheck your formulas and experiment with smaller ranges or simpler criteria until you are comfortable. Excel has many more specialized functions, but having a solid grasp of these core math, statistical, and logical tools will enable you to build robust spreadsheets for almost any purpose. Keep exploring new features and do not hesitate to dig deeper into Excel's documentation or online forums when you get stuck. Over time, you will find that what first seemed intimidating becomes second nature, making you much more productive and accurate in your work.