Using Excel for Accounting: IF Function for Accounting

Name: Using Excel for Accounting: IF Function for Accounting
Author: Bob Akbar

Learn how to use the IF functions, Pivot Table, and Average functions in Excel

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

In this lesson, I will show you average if and weighted averages. The first thing that we'll notice is that we have points of what they're worth. We have points that are earned and a simple average does not work because each type has a weight. So in other words, 10 points for homework is not worth the same as 10 points in the final. And if we do an average, a regular average, our points come off as an absolute disappointment to the students. So what we're going to do is first we're going to do the sum if and see what the possible points are for each type of assignments. Remember, the range of the criteria go together. And finally we have the sum range which is what we're going to sum, based upon our previously defined criteria, and there it is. We're gonna do the same thing with the points earned this time. And there we are. So on the homework, the student earned 81%. Next we'll do the quiz and we have 80% that the student has earned on the quiz. You will notice our divide by zero errors which occur if we are dividing by something that is zero. If we take a look at the formula for here we are dividing D 18 which is in blue, by C 18, which is in red, and C 18 is zero. That will produce a divide by zero error. It does not matter if you physically put in the number zero, if it's empty or if it's zero, you cannot divide it by zero. That is a mathematical error, and we will proceed, we'll do it our midterm and final. And you notice once we have the number that's being divided by filled the number now becomes zero because zero divided by any number is zero. And on the midterm we have 92% job well done. And finally, for the final, please notice the range and the criteria, they go together. The criterion must be whether the first range defined in this formula. And finally, we do the sum range, and there it is. Now what we're going to do is we're going to transfer these values over to here. And we'll notice there is no project in this term. So that will be a zero. And there is the final grade. The final grade you will notice the function is called sum product. And it takes the earned, weighted, and it divides it by the sum of the weighted. So we take the earned, the weighted and we divide it by the sum of the weighted. And there it is, to review, this is used when not all averages are equal. It is used when it comes to grading performances. It is also used in finance and accounting to judge what is the best use of resources to produce the best results. Thanks for watching. Stay tuned for the next lesson where I will show you filtering based upon ranges.

Intermediate Provider : Bob Akbar 3 Lessons · 17m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

The conditional filtering of data is an important part of accounting. It allows us to examine various categories, types, or items within the data, in order to make an analysis. These lessons will explore the various IF functions available in, and the way these can be used to can create formulas that filter data for the desired data subset. We will also look at the two types of averages that exist in the accounting world. In a normal average, all data is considered equal as it is measured, calculated and manipulated. In a weighted average, some data subsets are worth more, and others are worth less. These lessons will show you how excel calculates a weighted average, and how to use these averages properly. Analyzing financial data often requires filtering or giving a weighted average. In this course you’ll learn how to use the IF Function to filter and provide averages when Accounting in Excel.

What You'll Learn

Identify normal and weighted averages used in accounting
Define and apply the proper IF function for filtering data
Recognize filtering techniques to isolate the desired data subset
Use the WhatIF and CountIF functions in Excel
Calculate a weighted average using AverageIF
Filter accounting data based upon ranges using Pivot Tables

Key Takeaways

Conditional filtering of data lets you examine various categories, types, or items within data to perform accounting analysis.
The IF functions in Excel can be used to create formulas that filter data for a desired data subset.
In a normal average all data is considered equal as it is measured, calculated, and manipulated, while in a weighted average some data subsets are worth more and others worth less.
Analyzing financial data often requires filtering or giving a weighted average, and the IF Function can be used to do both when accounting in Excel.

Frequently Asked Questions

What will I learn in this course?

You will learn how to use the IF Function to filter data and provide averages when accounting in Excel, including identifying normal and weighted averages, defining the proper IF function, recognizing filtering techniques, and utilizing Pivot Tables.

Which Excel functions and topics does this course cover?

The lessons cover WhatIF, CountIF, AverageIF, and filtering based upon ranges, along with normal and weighted averages and Pivot Tables.

Who is this course for?

It is for those working with accounting data in Excel who need to filter financial data or calculate weighted averages, building skills in Microsoft Excel, Accounting, Accounting Analysis, Basic Accounting Functions, Excel Formulas, and Financial Accounting.

What is the difference between a normal and a weighted average in this course?

In a normal average all data is considered equal as it is measured, calculated, and manipulated; in a weighted average some data subsets are worth more and others are worth less, and the course shows how Excel calculates a weighted average and how to use these averages properly.