Sign in to continue learning
Data Analytics Foundations
27h23m
Say you want to look at the relationship between the number of adults and the number of children staying in the hotel. Let's see a powerful way to do that. In other words, what you want to do is, if the number of adults equals zero, add the number of children up. For this purpose, you can use SUMIF, another useful function. SUMIF adds up all the cells in a particular range if those cells meet a certain condition. You can also check cells in one range and add cells in another. Like COUNTIF, SUMIF is also a conditional. Sum these values only if a certain condition is met. Here's how it works. First, an equal sign. Then, SUMIF, the name of the function, open parentheses. SUMIF has three inputs, range, condition, and sum range. Select the range you want to check. This is what comes right after your IF. So in this case, number of adults. And then in quotes, add the criteria. You want equals zero. And then the column you want to add up is children. Let's see that in action. Let's sum the number of children who stayed with and without an adult. So let's start with the children without an adult. Start with the equal sign. Start the SUMIF function. And then select the number of adults. And then add the condition, which is equals zero. And then lastly, add up the number of children. Hit return. Okay. So in this case, you can see there's about 282 children who stayed on their own. How does that compare with accompanied children? Let's sum if adults are greater than zero. So I'm going to start a new formula. SUMIF. Select the number of adults. Add the condition, greater than zero. And then select the number of children. Close parentheses and hit return. There's over 3,500 children who were accompanied by an adult. Let's total up the children in this data set. In this case, I'm going to tab to accept since this suggestion looks correct. Let's calculate what percent is 282 children. So I'm going to do equals and divide the number of children without an adult by the total. There's about 7.38% of children who traveled without an adult. So about 92% of children traveled with an adult. Interesting. And you can also total these percentages, which should add up to 100%. So now you've segmented your data about children based on whether they were with an adult or not. Say you wanted to take the average of one column rather than the sum based on a particular condition. For example, you might want to investigate the average lead time of bookings that were and were not canceled. For that purpose, you can use SUMIF's cousin, AVERAGEIF. AVERAGEIF has the exact same inputs as SUMIF, except it will take the average of all the values that meet the condition rather than adding them all up. Let's get right into it. Maybe lead time can give us a hint about what kind of person cancels. What's your prediction? Do you think people who cancel tend to book farther in advance or closer to the date? Start by calculating the average lead time for bookings that canceled. So I'm going to select the canceled row, since that forms our condition. Type in canceled and then calculate the average lead time. On average, bookings that were canceled were booked about 139 days in advance. Format this as a number so that we don't have so many digits. Let's see how that compares to reservations that are not canceled. Start with equals, AVERAGEIF, select the booking status. We want to check for not canceled, and then again calculate the average lead time. Again, format as a number. On average, for not canceled reservations, the lead time is much shorter, only around 58 or 59 days. That's a big difference. What's the overall average? This will weight more heavily towards not canceled bookings, since they make up about two-thirds of bookings overall. So for this, we can just use the AVERAGE function and select the lead time. So on average, all bookings have about 85 days of lead time. What do you think could explain this difference? Here's one potential interpretation. Canceled bookings were reserved, on average, about four months in advance, which suggests to me that these were planned vacations where the plans must have changed. On the other hand, bookings that were not canceled were only booked about two months out. At that point, plans were probably more stable. Regardless, lead time seems to be related to cancellation in some way, although we can't know exactly how without further analysis. Great work on that segmentation task. Follow me to the next video to learn a similar technique that counts and sums based on multiple conditions.