statistics Archives - HowtoExcel.net

How to Calculate Inflation, Core Inflation, and CPI in Excel

January 6, 2024

by howtoexcel financial analysis statistics

Inflation is a critical economic indicator, reflecting the rate at which the general level of prices for goods and services is rising, and subsequently, how that erodes the purchasing power of money. To gauge inflation accurately, economists and policymakers rely on various metrics, with the Consumer Price Index (CPI), the Personal Consumption Expenditures Price Index (PCE), and the Core PCE being the most prominent. Each of these measures has its unique methodology and scope, making them useful in different economic contexts.

Let’s start with breaking down how these different measures are calculated, and what their strengths and weaknesses are.

Consumer Price Index (CPI)

The CPI, published by the Bureau of Labor Statistics, is one of the most widely recognized measures of inflation. It calculates the average change over time in the prices paid by urban consumers for a basket of goods and services. This basket includes a wide range of items such as food, clothing, shelter, fuels, transportation fares, charges for doctors and dentists’ services, drugs, and the other goods and services that people buy for day-to-day living. Prices are collected monthly from about 75 urban areas across the country, from about 6,000 housing units and approximately 22,000 retail establishments.

The strength of the CPI lies in its detailed breakdown of expenditure categories, which makes it a useful tool for understanding the impact of inflation on consumers. However, it has its limitations. For instance, it does not account for changes in consumer behavior or substitutions they make in response to price changes. Also, the CPI focuses only on urban consumers and may not accurately represent the experience of people in rural areas.

Personal Consumption Expenditures Price Index (PCE)

The PCE, published by the Bureau of Economic Analysis, measures the prices that people living in the United States, or those buying on their behalf, pay for goods and services. Unlike the CPI, the PCE includes all goods and services consumed by households, including those paid for by third parties such as employer-provided healthcare. The PCE is calculated by using data on nearly all goods and services businesses sell to households and on the incomes that households receive from business and from government.

One key advantage of the PCE is its ability to reflect changes in consumer behavior and the substitutions they make, which the CPI does not fully capture. This makes the PCE a broader measure of inflation. However, its wide scope can sometimes dilute the impact of price changes in specific categories, which might be more apparent in the CPI.

Core PCE

The Core PCE Price Index is a version of the PCE index that excludes the more volatile and seasonal food and energy prices. By excluding these items, Core PCE provides a clearer picture of the underlying inflation trend and is less subject to short-term volatility. This makes Core PCE a preferred metric for policymakers, including the Federal Reserve, when making decisions about monetary policy. This metric is often referred to as ‘core inflation.’

The exclusion of food and energy prices can be both a strength and a weakness. While it offers a more stable view of inflation, it can sometimes underrepresent the actual burden on consumers, especially during periods when food and energy prices are rapidly changing.

Calculating CPI, PCE, and Core PCE

Now that we know what these metrics are, let’s grab that data and plot them in a chart, to see how they have been trending in recent periods.

CPI can be downloaded from www.bls.go/cpi. The link to get the historical data can be found at https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years. You can follow along with this post on how to calculate the inflation rate based on this data set with Power Query.

PCE and Core PCE can be downloaded from the stlouisfed website. Below are the follow links you can use to access the data for each of them: https://fred.stlouisfed.org/series/PCEPI and https://fred.stlouisfed.org/series/PCEPILFE. This data can easily be downloaded from the Fred website and compared both metrics.

For the % change, take the current month value and divide it by the same value in the prior-year month and deduct 1. Here’s how all the data points and inflation rates look when compared against one another:

With that data set up, it’s now easy to plot the different inflation rates on a chart to see how they have varied and converged over the past year and a half:

If you liked this post on How to Calculate Inflation, Core Inflation, and CPI 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.

How to Make a Histogram Chart in Excel

August 1, 2023

by howtoexcel charts statistics

What is a histogram?

A histogram is a type of chart used to visualize the frequency distribution of a dataset. It represents how often different values occur within specific intervals or “bins” in a dataset. Histograms are particularly useful when you want to understand the distribution of continuous or discrete data and identify patterns, trends, or outliers in the data. They provide a clear and concise way to see the shape of the data and assess its central tendency and spread.

What the uses for a histogram?

Frequency Distribution

Histograms help you understand how data is distributed across different ranges or bins, revealing patterns or clusters in the data.

Identifying Outliers

With histograms, you can easily spot extreme values, or outliers, that may skew the chart.

Data Exploration

Histograms are great for data exploration and initial analysis, providing insights that may guide further investigation.

Data Comparison

You can compare multiple datasets or subsets of data to understand differences in their distributions.

How do you define bins for histograms?

One of the most important questions to ask yourself when creating histograms is how the bins should be defined and how big they should be.

Creating bins for histograms involves grouping the data points into intervals or ranges so that you can analyze the frequency distribution of the data effectively. The choice of the number of bins and their width can significantly impact the insights you gain from the histogram. There are various methods to determine the number and width of bins, and some common approaches include:

Square Root Rule

The square root rule suggests that the number of bins should be approximately the square root of the total number of data points. This method provides a simple way to determine the initial number of bins.

Sturges’ Formula

Sturges’ formula is a commonly used method to calculate the number of bins. It suggests that the number of bins (k) can be calculated as follows: k = 1 + log(n) where “n” is the number of data points. Sturges’ formula automatically adjusts the number of bins based on the data size.

Scott’s Normal Reference Rule

Scott’s rule considers the data distribution’s variability and suggests bin width based on the sample standard deviation (σ) and the number of data points (n): bin width = 3.5 * σ / (n^(1/3))

A larger standard deviation or more data points will result in wider bins.

Freedman-Diaconis’ Rule

This method takes into account the data distribution’s interquartile range (IQR) and the number of data points (n) to calculate the bin width.

Bin width = 2 * IQR / (n^(1/3))

The interquartile range is the difference between the 75th and 25th percentiles of the data.

Manual Selection

Depending on your domain knowledge and the specific insights you are seeking, you can manually choose the number of bins and their width. Adjusting the number of bins can highlight different aspects of the data distribution. With Excel, you can also do trial and error to see how many bins may be the best option for your chart.

When determining the bins, you should consider the following points:

Avoid too few bins, as this may oversimplify the data distribution and hide important details.
Avoid too many bins, as it may result in overfitting and obscure the underlying patterns.
Consider the data range and the resolution you want to achieve in the histogram.

Once you have determined the number of bins or their width, you can create the bins in Excel by manually specifying the bin ranges in a new column or using Excel’s built-in histogram function, which will automatically calculate the bins for you based on the data.

Creating a histogram chart in Excel

In creating a histogram in Excel, I’m going to use test scores on an exam as an example. This is an excerpt of my data set:

Here are the step-by-step instructions to creating a histogram chart in Excel.

Step 1. Select the histogram chart

Excel makes it easy to create a histogram. All you need to do is select the entire data set and then click on the option to insert a chart from the histogram section:

Selecting a histogram chart to insert into Excel.

As you can see from the preview, Excel has already set up some bins based on the data, so you may not even need to worry about setting them up yourself.

Step 2: Modifying your bins (if necessary)

This is the chart that Excel has created for me based my data set:

Automatically generated histogram chart in Excel.

It has created bins of equal size based on the data set. However, you may not agree with the cutoffs given they are a bit random (e.g. 8-26, 26-44, etc.). To change this, you can right-click on the x-axis and select the option to Format Axis. From there, there is a section for the different bin options:

Modifying the bins on an excel histogram chart.

The default options is set to automatic. However, in this situation you may want to consider using either a set bin width or changing the number of bins. As you can see from the greyed out numbers, Excel has created 5 bins with a width of 18 each. If you change the bin width to 10, then Excel starts from the lowest value of 8 and adds 10, and continues on:

Excel histogram chart with bin widths set to 10.

This has created 9 bins. But suppose you want 10 bins. You can change the number of bins to 10 manually. And when doing so, this is the chart Excel creates:

Now the bin width is set to 9. For test scores, this still may not be ideal, as the cutoffs don’t make logical sense. Ideally, they would be in increments of 10 and be round bin numbers. To fix this, what you can do is to set a bin width of 10. And then, set the underflow bin to 10. This means that anything less than or equal to 10 will fall into the first bin. This becomes a catch all for any values of 10 and under, even if the data starts at 8. Now, the histogram looks like this:

This is a much cleaner look with cutoffs that make more sense. One thing to note is that while there does appear to be an overlap in the bins, that’s not the case. For the (10,20] bin, it counts the number of values that are greater than 10 up to and equal to 20. For the (20,30] bin, it counts values greater than 20 that are up to and including 30.

Step 3: Apply formatting (optional)

Once you’re satisfied with the number of bins and their width, the last step is to change the formatting, assuming you want to change the look of it. This can involve changing the histogram’s colors, adding or removing gridlines, adding data labels, as well as any other changes you might normally make to a chart.

In my example, I’ve modified the title, added vertical gridlines, and added data labels to show the frequency count, and also removed the axis to the left:

If you liked this post on How to Make a Histogram Chart 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.

How to Calculate Combinations and Permutations in Excel

July 11, 2023

by howtoexcel statistics

Combinations and permutations are fundamental deal with the arrangement and selection of objects or elements from a set. While both combinations and permutations involve counting possibilities, they differ in terms of the order and repetition of elements. In this post, I’ll show you how to calculate both permutations and combinations in Excel, both with and without replacement and repetition.

What is a permutation?

A permutation refers to an arrangement of objects from a set, where the order matters. In other words, permutations consider the different ways in which objects can be arranged. For example, let’s take the set {A, B, C}. The permutations of this set would be ABC, ACB, BAC, BCA, CAB, and CBA. The number of permutations can be calculated using the formula:

nPr = n! / (n – r)!

Where n represents the total number of objects in the set and r denotes the number of objects being selected for each arrangement. The exclamation mark denotes factorial, which means multiplying a number by all the positive integers less than it down to 1.

Suppose you want to consider all 26 letters of the alphabet — this would be the n value. Now, if you want to know all the different permutations when selecting 3 characters, that would be your r value. To calculate the number of different permutations, your formula would be as follows:

nPr = 26! / (26 – 3)!

This gets simplified to:

nPr = 26! / 23!

Rather than doing this complex calculation, you can first cancel out the numbers going up until 23 on each side. Then you’re left with the following:

26*25*24 = 15,600

This tells us that there are 15,600 different permutations when selecting the three letters from the alphabet. The preceding formula assumes that you are not replacing objects. If, however, you are replacing them then that means you can select the same item multiple times. In this situation, the formula for calculation permutations with replacement is as follows:

=n^r

When choosing 3 letters from the alphabet, the result would be:

26^3 = 17,576

In this situation, you have will have more possible permutations since the objects can repeat.

What is a combination?

If you’re talking about combinations, then the difference here is that you don’t consider the order as you would with permutations. In the earlier example where the set was {A,B,C} and you needed to select three characters, there would only be 1 combination. That’s because whether it’s ABC, BAC, CAB, or any other order of the characters, that’s irrelevant since combinations don’t care about order. The letters are all the same, and thus, there would only be 1 possible combination.

The number of combinations can be determined using the formula:

nCr = n! / (r!(n – r)!)

Let’s do this again, when selecting 3 letters from the alphabet:

nCr = 26! / (23! (26-23)!)

That formula simplifies to this:

nCr = 26! / 23! (3)!

And again, up until 23, everything gets canceled out, leaving the following:

nCr = (26*25*24)/(3*2*1)

This is the same as the result from the permutation calculation but the difference is that the denominator is now larger; it is calculated as the r factorial. Upon completing this formula, the result is:

15,600/6 = 2,600

This is the number of permutations divided by the factorial of 3, the number of selections. If there are replacements and you select the same item multiple times, this is the formula:

(n + r -1)! / (r!(n-1)!)

This results in the following calculation:

(26 + 3 -1)! / (3!(26-1)!)

This simplifies to:

(28)!/(3!(25)!)

Which further simplifies to:

(28*27*26)/(3*2*1) = 3,276

How to calculate permutations and combinations in Excel

In the above examples, I showed you how you can calculate permutations and calculations manually. But with Excel, there are formulas that can do the work for you.

To calculate permutations, we use the PERMUT function when there are no replacements. In the first example where there were 3 items chosen from a set of 26, this is the PERMUT formula:

=PERMUT(26,3) => 15,600

It returns the same result. If there are replacements, then the PERMUTATIONA formula is used:

=PERMUTATIONA(26,3) => 17,576

For combinations, the default function is COMBIN:

=COMBIN(26,3) => 2,600

And when there are replacements, COMBINA is used:

=COMBINA(26,3) => 3,276

These results all match up with the calculations from the previous examples, where they were done manually.

How to calculate the Powerball odds

For the Powerball, there are two draws that happen. The first is where 5 numbers are chosen from 69 possible items. Then, in the second draw, there is 1 number that selected from 26 possibilities. When dealing with probabilities, when we want both events to happen, we need to multiply the odds. The first step will be to calculate the individual combinations. Since there is no replacement, the formula for the first draw is as follows:

=COMBIN(69,5) => 11,238,513

For the second draw, the calculations straightforward since there is only 1 item selected from 26 possible options, so there can only be 26 combinations. Thus, to calculate the Powerball odds where you win both the first draw and the second draw, you need to multiple the odds of winning the first draw by the odds of winning the second draw:

=11,238,513 x 26 = 292,201,338

This tells us that the odds of winning both draws are 1 in 292 million.

If you liked this post on How to Calculate Combinations and Permutations 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.

How to Show Percentages as Rates Per 100,000

February 16, 2022

by howtoexcel with No Comment statistics tips & tricks

When displaying data, using percentages isn’t always optimal, especially if you’re dealing with a very tiny number. In those cases, it may be more effective to display data as a rate per 100,000 or per 1 million, depending on how small of a number you have. Below, I’ll show you how you can move between percentages and rates.

Converting between percentages and rates

To convert percentages into rates, it’s as simple as multiplying the percent by the population you’re trying to calculate a rate for. Some common examples are calculating rates per 10,000, per 100,000, or per 1 million.

Let’s start with a simple stat: there are approximately 50 million Microsoft 365 subscribers in the world. Out of a global population of 7.9 billion people, that is 0.6% of the total population. Let’s frame this a different way, as a rate. To do this, I can multiply that percentage by 100,000, which returns a value of 633. That tells us that for every 100,000 people, 633 of them have Microsoft 365 subscriptions. You can multiply this by 10 to say that for every 1 million people, more than 6,300 will be subscribers.

Now let’ do the reverse. The odds of winning the Powerball jackpot are approximately 1 in 292 million. To convert this into percentages, we’ll need to divide 1 by 292,200,000. The result is a very tiny value of 0.0000000034. As you can see, this isn’t very helpful in using this as a percentage. And this is why using a rate is more appropriate.

Calculating 1 per a larger base

If you’re working with that incredibly small value, you can convert that into a rate of 1 per some larger number. All you need to do is calculate the inverse. To do that, take 1 and divide it by the value. In the above example, it would be 1/0.0000000034, which would return a value of 292 million.

Creating a quick template

If you have some small percentages you want to convert into percentages, you can create a quick template to help you determine which rate you may want to use. In some cases, you may not want to just use 1 per x but instead x per 100,000, or some larger figure. You’re communicating the same value, it’s just a matter of how you decide to do it.

Below, I’ve collected some data showing the percentage of dog owners in the world, the percentage of people who have green eyes, and the percentage of people with red hair:

Table showing percentage of dog owners, people with green eyes, and people with red hair.

I’m going to create four additional columns, one to calculate the inverse (1 per x), rate per 10,000, rate per 100,000, and per 1,000,000. For the last three columns, all you need to do is to multiply the percentage by those base numbers. And here’s what the results will look like:

A quick way to check these results is by calculating the percentages. 60,000/1,000,000 is equal to 6%, 20,000/1,000,000 is 2%, and 15,000/1,000,000 is 1.5%.

The advantage of using the larger population number is that your results will stand out more and can be easier to visualize on a chart:

Rates per population showing the occurrence of dog owners, people with green eyes, and people with red hair.

If you liked this post on How to Show Percentages as Rates Per 100,000, 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.

Compounded Annual Growth Rate Calculator

February 19, 2020

by howtoexcel with No Comment calculator investing statistics stocks

If you don’t want to calculate compounded annual growth rate yourself, you can do it quickly and easily with this free calculator:

Calculating Compounded Annual Growth (CAGR)

Percent Change
Number of Years

[WP-Coder id="1"]

How to Make a Correlation Matrix in Excel

May 3, 2019

by howtoexcel with No Comment advanced correlations statistics

To find correlations between data points is useful when you’re trying to find a pattern or any sort of relationship. Below, I’ll show you how you can quickly do a correlation matrix as well as how to do a calculation if you’re only looking at two data sets to compare.

Step 1: Enabling the Data Analysis Add-on

One of the biggest challenges in creating a correlation matrix is just finding where the option to calculate the correlations is. In order to access it, you need to first enable the Data Analysis add-on.

To do this, you have to get to the Excel Options. This will vary depending on which version of Excel you have, but in newer versions, you go to the File tab and select the Options button at the bottom of the page. Once there, you’ll want to select the Add-ins option.

From there, you’ll have a list of all the Add-ins available. Then, next to the Manage button at the bottom, click on the Go button (highlighted in yellow).

After clicking the button, you’ll have a list of all the Add-ins that you can install.

Click on the checkbox next to the Analysis Toolpak and then click OK.

Step 2: Running the Correlation Add-in

Now, if you go onto the Data tab, you should see off to the right, a button for Data Analysis, next to the Outline group.

Clicking on the Data Analysis button will give you a lot of different options, but for this example, we’re just going to use the Correlation option.

Step 3: Selecting the Ranges to Evaluate

Next, you’ll be asked to select your Input Range. This is where you’ll enter the ranges that you want to compare. You can select either rows or columns. In most cases, you’ll probably leave the default, which is columns. You’ll want to select the columns you want to compare and specify if the label is in the first row.

Once you’ve selected your data along with where you want to output the data (I usually leave the default, which is New Worksheet Ply), then click on OK.

If you don’t have numbers in all your columns, you might see the following error come up:

To fix this, you’ll need to look for any blank cells that might be in your data. If you have any if formulas that have a result of “”, then those will cause a problem as well. Either way, your data will need to be cleaned up to ensure that only numbers are in the range that you want to calculate correlations on.

Once you’ve cleaned it up, depending on how many columns you selected, you should end up with something that looks like this:

Step 4 (Optional): Apply Conditional Formatting to the Correlation Matrix

Although the matrix is technically complete, this is not an easy way to identify significant correlations, especially if you’re looking at several columns. This is where conditional formatting can help us.

What I’ll do is setup formatting so that anything between 0.7 and 0.99 shows up as green, and anything that is between -.1 and -.99 will be red to indicate a negative correlation. Now the matrix looks a bit easier to read since I can focus on areas of high or negative correlations:

correlation matrix conditional formatting excel

For a detailed look at how to do conditional formatting, refer to this post.

Recreate a Correlation Matrix Using a Formula

That’s how you can create a correlation matrix in Excel, but what if you just want to look at the correlation between two pairs of data sets? In that case, you can use the CORREL function.

Back to my data set, I can use the CORREL function and select two data sets.

After hitting enter, it tells me the correlation of the two columns is 0.61. The one limitation of this is that you can only compare two data sets at a time. However, you don’t have to go through data analysis feature and can use this to put the correlation results in any way that you want.