averages Archives - HowtoExcel.net

How to Calculate Weighted Average in Excel

May 9, 2023

by howtoexcel average averages financial analysis

A weighted average is a type of average that assigns different weights or values of importance to each element in a dataset. Unlike a simple average that treats all elements equally, a weighted average adjusts the contribution of each element based on its relative significance. This means that some elements have a greater impact on the final result than others, depending on their weights.

Why use a weighted average?

Weighted averages are useful because they provide a more accurate representation of the data by taking into account the importance of each element. For example, in financial analysis, a weighted average may be used to calculate the average interest rate of a portfolio of loans or investments, where the weight of each loan or investment is based on its size or duration. In schools, a weighted average may be used to calculate a student’s overall grade by assigning different weights to assignments, quizzes, and exams based on their importance or difficulty. Anytime you don’t want everything to have the same weighting or importance is when you’ll want to use a weighted average.

Calculating a simple weighted average in Excel

A common way to apply a weighted average is by using a points system. Suppose you are looking to buy a house and have many different criteria that you want to take into consideration, such as square footage, location, if it has a basement, etc. But not all of these items are equally important, and so you may want to say that location is worth 30 points and square footage is worth 25 points, and so on.

The first step is to assign a weight, or point value, to each one of these criteria. Then, assign a score to each one of them criteria, perhaps within a range of 1 to 100. Once you’ve done that, you multiply the score by the points. Total that up, and divide it by the total points, and you’ve got your score, or weighted average. Here’s an example:

Sample weighted average calculation when evaluating the purchase of a house.

This particular house scored high on the most important items, and thus, resulted in a high weighted average. The total of the score x points column was 10,190. Taking that value and dividing it by 145, the total points, results in a weighted average of 70.28.

Here’s another house, which scores far lower, with a weighted average of just 45.48:

Although it scored high on areas such as school and kitchen, because of its low scores on the top two weightings — location and square footage — that kept its weighted average down.

Creating a template like this in Excel and comparing your different scores can be a way to help compare houses and other things, while giving each criteria an appropriate weighting. By simply scoring everything on a value of 1-100 without weighting, the problem would be that each criteria would effectively be equal, saying that things like layout and the garage are just as important as the location and size of the house, which most people likely wouldn’t agree with. By using weights, you can better take into account the value of each individual criteria.

Calculating grades using weighted averages

Another use for calculating weighted averages is when it comes to grading. In a class, you might have a specific weighting scale that says assignments are worth 10% of your grade, quizzes are 20%, a project is worth 5%, a mid-term is 25%, and the final exam accounts for 40%.

In this case, you’re using percentages that add up to 100% rather than weights, which may be more subjective. This still works in largely the same way as you are multiplying a score by the weight. Except now, since the weights add up to 100%, you don’t need to worry about taking the total and dividing it by the total weights. Whatever your result is, that is the total score. Here’s an example of how a student scored in a class:

Calculating a student's grade using a weighted average calculation.

When using percentages for weighting, it’s important to double check they add up to 100% to ensure everything is accounted for. In this example, the student had a score of 72.25, which would be the same as saying they scored 72.25%, which would be their grade for the course. As you’ll notice, the student’s high scores on the quizzes and mid-term exam were unfortunately offset by a poor final exam mark.

In this example, since we’re just looking at percentages, you can do without the extra column for value, which takes the weight x the score. Instead, you can use SUMPRODUCT. If the weightings are in cells A2:A6 and the scores are in B2:B6, the grade can be calculated with the following formula:

=SUMPRODUCT(A2:A6,B2:B6)

The formula will multiply each value by the corresponding value in the same row, thereby eliminating the need to use an extra column. By using an Excel formula, you can save yourself the extra step of having to tally up the values and then dividing them by their weights again.

If you liked this post on How to Calculate Weighted Average in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

15 Excel Functions Accountants Should Know

March 28, 2023

by howtoexcel accounting advanced average averages beginner count countif dates functions index intermediate lookup lookups match sum sumif sumifs unique vlookup

If you’re an accountant, you know that working with large amounts of data can be a daunting task. But with Excel, that work can get a whole lot easier and more efficient. Understanding Excel’s advanced features and functions can improve productivity, reduce errors, make your work more accurate, and most importantly — save you time. Below, I’ll go over some of the most important Excel functions that accountants should know, and provide examples of how to use them. For this example, I’ll use the following spreadsheet. Feel free to download it and follow along with the calculations.

1. SUM

The SUM function is a basic but essential function in Excel. It allows you to add up a range of values, which is helpful when calculating totals, such as revenue, expenses, and profits. Suppose you have a spreadsheet with sales data. In the above example, the total sales are in column G. If you wanted to sum up the entire column, the formula would be as follows: =SUM(G:G)

2. AVERAGE

The AVERAGE function calculates the average of a range of values. It is useful when analyzing data and preparing financial statements. In the above example, suppose you wanted to calculate what the average sale was. To do this, you can just use the AVERAGE function on column G, similar to the SUM function before. Here’s the formula: =AVERAGE(G:G)

3. IF

The IF function allows you to test a condition and return one value if the condition is true and another value if the condition is false. This can be useful because it can send your formulas to the next level. By knowing to use the IF function, you could also use SUMIF, AVERAGEIF, and many other functions that involve an if statement. In the above example, let’s say you only wanted to know if a value in cell M2 was part of the Motorcycles product line. The formula would be as follows: =IF(M2=”Motorcycles”,1,2). If it is part of Motorcycles, you would have a value of 1, otherwise, it would be 2.

4. SUMIF

By knowing the SUM and IF functions, you can combine them together with SUMIF, which is an incredibly popular function. It gives you a quick way to tally up the totals that meet a criteria. For example, let’s say you want all sales that relate to the Motorcycles category. The formula for that would be as follows: =SUMIF(M:M,”Motorcycles”,G:G). If the criteria is met in column M, then the formula will sum up the corresponding values in column G. There’s also the super-powered SUMIFS function, which allows you to combine multiple criteria.

5. EOMONTH

The EOMONTH function calculates the last day of the month for a specified number of months in the future or past. It is useful when working with data that is organized by date. For accountants, this can be useful when you’re calculating when something is due. Let’s say in this example, we need to calculate the date orders need to go out on, and that needs to be the end of the next month. Using the ORDERDATE field in column H, here’s how that calculation would look in the first cell, which would then be copied down for the rest: =EOMONTH(H2,1)

6. TODAY

The TODAY function is helpful for accountants in calculating deadlines and knowing how many days are remaining or past a certain date. Suppose that you wanted to know how many days have past since the ORDER DUE DATE that was calculated in the previous example. Rather than entering in a static date that every day you would need to change, you can just use the TODAY function. Here’s how a formula calculating the days since the deadline for the first cell would look like, assuming the due date is in column N: =TODAY()-N2. The next day you open up the workbook, the calculations will update to reflect the current date; there’s no need to make any changes. There are many more date calculations you can do in Excel.

7. FV

The FV function calculates the future value of an investment based on a fixed interest rate and a regular payment schedule. You can use it to calculate the future value of an investment or savings account. Let’s say that you wanted to save $10,000 per year and expect to earn a return of 5% per year on that investment. Using the FV calculation, you can do that with the following formula: =FV(0.05,5,-10000). If you don’t enter a negative for the payment amount, the formula will result in a negative value. You can also specify whether payments happen at the beginning of a period (1) or end (0 — this is the default) with the last argument in the function.

8. PV

The PV function lets you do the opposite and work backwards from a future value to the present. Knowing that the calculation in example 7 returns a value of $55,256.31, that can be used in the PV calculation to check our work: =PV(0.05,5,10000,-55256.31). The formula returns a value of 0, which is correct, as there was no starting value in the FV calculation.

9. PMT

The PMT function calculates the periodic payment required to pay off a loan with a fixed interest rate over a specified period. It is helpful when determining the monthly payments required to pay off a loan or mortgage. Let’s take the example of a mortgage payment where you need to pay down $500,000 over the period of 30 years, in monthly payments. At a 5% interest rate, here’s what the payment calculation would be: =PMT(0.05/12,12*30,-500000,0). Here again the ending value needs to be a negative to avoid a negative value in the result. And since the payments are monthly, the periods need to be multiplied by 12 and the interest rate is dividend by 12.

10. VLOOKUP

The VLOOKUP function allows you to search for a value in a table and return a corresponding value from another column in the same row. It’s one of the most common Excel functions because of how useful and easy to use it is. It is helpful when working with large data sets and performing data analysis. Let’s suppose in this example that you want to find the sales related to order number 10318. The formula for that calculation might look like this: =VLOOKUP(10318,C:G,5,FALSE). In a VLOOKUP function, you need to specify the column number you want to extract from, which is what the 5 represents. If you’re using Office 365, you can also use the newer, flashier XLOOKUP function. I put VLOOKUP on this list because it’ll work on older versions of Excel — XLOOKUP won’t.

11. INDEX

The INDEX function allows you to return a value from a data set by specifying the row and column number. It’s also helpful if you just want to return data from a single row or column. For example, the sales column is in column G. If I know the order number is on row 20 (which relates to order number 10318), this formula would do the same job as the VLOOKUP in the previous example: =INDEX(G:G,20,1).

12. MATCH

The MATCH function allows you to find the position of a value within a range of cells. Oftentimes, Excel users deploy a combination of INDEX and MATCH instead of VLOOKUP due to its limitation (e.g. VLOOKUP can’t extract values to the left of the lookup field). In the previous example, you had to specify the row belonging to the order number. But if you didn’t know it, you could use the MATCH function within the INDEX function. The MATCH function would look like this: =MATCH(10318,C:C,0). Placed within an INDEX function, it can replace the argument where in the previous example, we set a value of 20: =INDEX(G:G,MATCH(10318,C:C,0),1). By doing this, you have a more flexible version of the VLOOKUP function. You can also create dynamic formulas using INDEX and MATCH that use lookups for both the column and row.

13. COUNTIF

The COUNTIF function allows you to count the number of cells in a range that meet a specified condition. Let’s count the number of values in the data set that are Motorcycles. To do this, you would enter the following formula: =COUNTIF(M:M,”Motorcycles”).

14. COUNTA

The COUNTA function is similar to the previous function, except it only counts the number of non-empty cells. With no criteria, it is helpful to just the total number of values within a range. To calculate how many cells are in this data set, you can use the following formula: =COUNTA(C:C). If there are no gaps in data, then the result should be the same regardless of which column is used. And when combined with the UNIQUE function, you can have an easy way to count the number of unique values.

15. UNIQUE

The UNIQUE function returns a list of unique values within a range, and it’s a much easier method than the old-school way of extracting unique values. If you wanted to extract all the unique product lines in column M, you would enter the following formula: =UNIQUE(M:M). If, however, you just wanted to count the number of unique values, you could embed it within the COUNTA function as follows: =COUNTA(UNIQUE(M:M)). You can adjust your range if you don’t want to include the header.

This is just a sample of some of the useful Excel functions that accountants can utilize. If you are familiar with them, you’ll put yourself in a great position to improve the efficiency of your workflow and make your spreadsheets easier to use. Plus, you can confidently say that you are highly competent with Excel, which can make your resume more attractive and make you better suited for accounting jobs that require advanced Excel skills — and there are many of them that do!.

If you liked this post on 15 Excel Functions Accountants Should Know, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

How to Do a Moving Average in Excel

June 13, 2020

by howtoexcel with No Comment averages intermediate offset

If you’re doing any type of data analysis, a moving average can be useful in smoothing trends and normalizing data. Below, I’ll show you how you can easily create a moving average in Excel. The data will again be based on the retail and food services sales data that I used in my post covering how to transpose data.

With the data now converted from the summary table, it’s in the right format for analyzing and averaging the data.

Calculating an average

To calculate an average in Excel, all you need to do is use the AVERAGE function. Simply select the range that you want to average, and the function will take care of the calculation for you. Here’s an excerpt from the data set:

If I wanted to calculate the average for the year 1992, I could total the values relating to those years and then divide it by 12. The total comes to $2,007,617 and after dividing it by 12, I get an average of $167,301.42. But I can skip the step that involves dividing the data by using the Average function. Here’s a look at the difference:

However, there’s a way we can simplify this even further. In this example, we still have to manually select the range that we want to average, and that’s not going to be optimal if you want to pull the average for every year. It’s also easy to make a mistake.

What we can use is the AVERAGEIF function in Excel. That function allows you to add criteria to your average. Here, we’ll use the year, 1992, as the criteria. And by doing so, we don’t have to worry about selecting the right cells since we can just select the entire column:

You’ll notice the year is hardcoded, which isn’t ideal. Here’s how we can fix that while also making it easy to pull the average by year:

This is a great way to average by year, but it’s still not a moving average. Let’s do that next.

How to make the average move with your data

Companies often track sales numbers for the last 12 months, also referred to as the trailing twelve months (TTM). It’s actually easier to do than averaging by year since there’s no criteria — you’re simply averaging the last 12 months. The one limitation here is that you have to have 12 months of data before you can start. Here’s how that would look in our example:

It’s as easy as just selecting the previous 12 cells in the range, averaging them, and copying the formula down. However, you don’t have to select the data range, even if it is just a one-time thing. If you include the OFFSET function, you can make your formula a lot more adaptable and flexible. It allows you to move your data set, and you can also determine how many values you want to include in your average.

Here’s how a TTM calculation could look like using OFFSET:

Moving average calculation using offset.

This is a more complicated formula so let’s breakdown what’s happening here. The OFFSET function has multiple arguments, here’s a list of them:

Reference
Rows
Columns
Height
Width

The reference is just your starting point. In the first formula, I used cell C13 as that was the current value. It wouldn’t make sense to use data from a different row and I also want to make sure it’s in the value column since that’s where I want my data to come from.

Next, is the rows argument. I put -11 in this case because I want to start 11 rows higher than where the reference cell (C13) is. Moving 11 cells up would put me at C2, which is the first data point. The columns argument is left at 0 because I want to remain in the same column.

The height argument is key here because I want to ensure my data set contains 12 values in it. Without this argument, I would simply get the value from cell C2, $164,095. You can ignore the width argument or set it to 1, since you don’t need to include other columns.

If your data is organized in columns rather than rows, all you’ll need to do here is to switch around the arguments (e.g. rows would be in columns, height argument would go into width, etc.)

The last part of the formula is enclosing it within the average function so that it calculates the average.

Changing the number of periods you want to average

The big advantage of using the OFFSET function is now it’s a lot easier to manipulate your data and change how many values you want to include in your calculation. Suppose that instead of the last 12 months, we wanted to do a three-month average. All that would include is tweaking the OFFSET function so that it goes two rows back and includes three values. Here’s how that would look:

The key takeaway here is that the number of rows or columns that you want to go back will be one less than the number of rows or columns you want to include. The only exception would be if you don’t want to include the current month’s data. For instance, if in the December moving average you didn’t want to include December’s data and wanted to go from September-November, then you would offset three rows rather than two.

How a moving average can help smooth trends

If we were to look at the data since 2018, here’s how it would look in a chart:

It’s fairly stable and there aren’t any big jumps from one month to the next. Here’s how it would look with a three-month moving average:

Monthly sales data on a chart along with a six-month moving average.

Using the moving average, we can see what the longer-term trend is. You’ll notice that in the drop off that happened in March and April, the average goes on a much smaller decline because it’s still including earlier months in the calculation. And if we use a six-month average, then there’s even less of a dip in the trend:

Which period you use for a moving average will ultimately depend on how much smoothing you want. A short timeframe will be more volatile than a longer one, but a longer one may take too long to capture any changes.

That’s a quick overview of how to calculate moving averages in Excel. As you can see, you can just quickly grab an average or you can build a versatile formula using the OFFSET function which can make your calculations easier to change in the future.

If you liked this post on how to do a moving average in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.