There are times, however, when you only want to perform calculations if the inputs meet one or more criteria. In this video, learn how to use conditional functions to perform specific calculations when those criteria are met.
- [Instructor] Most of the formulas you create in Excel will always perform the same calculation. For example, you might want to multiply a sales total by 5% to compute a commission. There will be times, however, when you only want to perform calculations if the inputs meet one or more criteria. In this movie, I will show you how to use conditional functions to perform specific calculations when those criteria are met. My sample file is the Conditional Workbook, and you can find it in the Chapter Five folder of the exercise files collection.
This workbook has two worksheets, and I'm starting on the Commissions worksheet. This worksheet displays sales data for a number of sales reps versus their goal for the month. What I want to do is to calculate the commission. If they met their goal, then they get 7%, if not, they receive 5%. I'll start in cell D2, which for me is currently selected, typing equal sign. I want to create a conditional calculation, so I will use the If function.
So I'll type an equal sign followed by If, I-F, then a left parenthesis. Now, I want to perform a logical test. I'm testing if the value in C2, which is actual sales, is greater than the goal in B2. And this is for the first representative. I'll copy the formula later. So the logical test, is C2 greater than or equal to B2. If they meet their goal, then that's fine, then a comma. The value, if true, for the commission, will be their actual sales, C2, multiplied by 7%, comma.
For false, if not, then the value will be C2, actual sales, multiplied by 5%, right parenthesis and return. And I get a commission of $9,100. That's 5% of 182,000, and it's only 5% because the salesperson did not meet their goal. If I click cell D2 and double-click that cell's fill handle, that's the green square at the bottom right corner.
Double-click that. When my mouse pointer changes to a black cross-hair, and I get the calculation for each of the salespeople. And that is how you create conditional formulas using the If function.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.