Log In to continue learning
Data Analytics
Beginner
Topics
Data Engineering
Data Processing
Synthetic Data
Data Science
Let's see how you can use a spreadsheet to calculate central tendency in order to answer a business question. If you need a refresher on spreadsheet functions, I encourage you to revisit the Data Analytics Foundations course, where you can learn all the functions you'll see in this demo. This spreadsheet picks up where you left off with the Lenning Tree loan data. Recall that you previously created a histogram for the paid interest feature, essentially how much profit had accrued on the loan. Let's complement the histogram with some descriptive statistics to help you get an idea of how much money you can be making from the interest on loans. And don't forget, if you'd like to follow along with this demo, you can find this spreadsheet and the solution in the Downloads tab. You have a new sheet here to keep your statistics separate. A good starting point is always the mean. The point of these descriptive statistics is to calculate some specific measures that help summarize that distribution. To calculate the mean of the paid interest, you can use the AVERAGE function. Remember to start each formula with equals. Open parentheses, refer back to the original data, and then select the paid interest column. On average, about $617 was paid out on each loan. Next, you can calculate the median. You can enter the formula for median, and again select the paid interest column. The median paid interest is lower than the mean. The fact that the mean is higher than the median suggests there might be some higher paid interest values in the data. Next, you might want to calculate some percentiles to identify some additional reference points in the distribution. To calculate the 10th percentile, you'll use the PERCENTILE function. Select the paid interest column, and then you need to enter the percentile. The percentiles need to be represented in a number between 0 and 1. So for the 10th percentile, you'll enter 0.1. The result is around $117. To calculate the 50th percentile, you'll use the same approach, except change the percentile parameter. Notice that the result is the same as the median. The median is the 50th percentile in the data. Finally, imagine you're a lender and you're interested in what the highest paid interest might be on a particular loan. You might want to calculate the 99th percentile. One of the highest paid interest amounts on a loan is over $2,400. This is the top 1% in the overall distribution. Suppose you're interested in understanding the collateral behind the loans by examining homeownership. Let's take a look back at the data. In column D, you can see the original homeownership column, which includes categories of rent, home, and mortgage. It turns out you can't really find the median of this feature, or the average. You'd like to find the mode of this variable. The mode function only uses numbers. So you can use this homeownership numeric column, which has 1 for rent, 2 for mortgage, and 3 for own. You can use the mode function to calculate the most common category. Here you get a mode of 2, which again corresponds to having a mortgage. So most people taking out a loan have a mortgage. So this helps you get a better picture of where the people taking out loans are in life, and the kind of collateral they might have. By calculating central tendency for the paid interest, you're starting to understand the center of mass of the sample data, which gives you a sense of roughly what payout you can expect on average. Once you've completed the practice assessment and the practice lab for this lesson, join me in the next one to learn more about variability and skewness in your data.
