Mode
To find the mode, you can use the countif for each possible value if the range of values is small enough (i.e., you don’t want to do a count if each of 50 possible values). Then you have the count for each value and can compare to see what happens the most.
Another option is to use the mode.mult function. If this is discrete data, this is easy. If it is categorical, then you have to convert your strings into numbers.
For instance, for minimum_nights:
=mode.mult(k2:k48896)
You should get “1” as the most common minimum night. If there is a tie, however, (e.g., most common test score had two most common values of 9 times occurring), then you would get of those values returned.
You write the range as the column letter plus the cell number. Notice that examples start at cell 2 and not cell 1. That is because you don’t want to count what is in the column title, which is often in the first cell of the column. Depending on your data set, you might want to start later if there are a few rows above the beginning of the entry of values.
Median
As we will see on the next page, sometimes it can be really valuable to look at the median if your values are distributed in a certain way.
The median is just the value that is at the 50th percentile. In other words, 50 percent of the values are below the median value–it occurs “in the middle” in the order of the scores.
The mode for price of Airbnb listings in NYC in 2019 is $100, but the median is $106. These are pretty similar, which is a good sign for the variance (so far!) in this distribution (something we will talk more about soon).
To do this in Excel, you use the following:
=median()
In the parentheses, put the cell range (for price in Airbnb, it would be j2:j48896).
Mean
Mean is just adding up all values and dividing by how many values there are.
The formula in Excel is =average. For example, to find the mean for price, you use:
=average(k2:k48896)
Doing that, you get $152.72