#### Overview of this book

Data Forecasting and Segmentation Using Microsoft Excel guides you through basic statistics to test whether your data can be used to perform regression predictions and time series forecasts. The exercises covered in this book use real-life data from Kaggle, such as demand for seasonal air tickets and credit card fraud detection. You’ll learn how to apply the grouping K-means algorithm, which helps you find segments of your data that are impossible to see with other analyses, such as business intelligence (BI) and pivot analysis. By analyzing groups returned by K-means, you’ll be able to detect outliers that could indicate possible fraud or a bad function in network packets. By the end of this Microsoft Excel book, you’ll be able to use the classification algorithm to group data with different variables. You’ll also be able to train linear and time series models to perform predictions and forecasts based on past data.
Preface
Part 1 – An Introduction to Machine Learning Functions
Free Chapter
Chapter 1: Understanding Data Segmentation
Chapter 2: Applying Linear Regression
Chapter 3: What is Time Series?
Part 2 – Grouping Data to Find Segments and Outliers
Chapter 4: Introduction to Data Grouping
Chapter 5: Finding the Optimal Number of Single Variable Groups
Chapter 6: Finding the Optimal Number of Multi-Variable Groups
Chapter 7: Analyzing Outliers for Data Anomalies
Part 3 – Simple and Multiple Linear Regression Analysis
Chapter 8: Finding the Relationship between Variables
Chapter 9: Building, Training, and Validating a Linear Model
Chapter 10: Building, Training, and Validating a Multiple Regression Model
Part 4 – Predicting Values with Time Series
Chapter 11: Testing Data for Time Series Compliance
Chapter 12: Working with Time Series Using the Centered Moving Average and a Trending Component
Chapter 13: Training, Validating, and Running the Model
Other Books You May Enjoy

# Grouping data in segments of two and three variables

Now, we are going to segment data with two variables. Several real-world problems need to group two or more variables to classify data where one variable influences the other. For example, we can use the month number and the sales revenue dataset to find out the time of the year with higher and lower sales. We will use online marketing and sales revenue. Figure 1.8 shows the four segments of the data and the relationship between online marketing investment and revenue. We can see that segments 1, 2, and 4 are relatively compact. The exception is segment 3 because it has a point that appears to be an outlier. This outlier will affect the average and the standard deviation of the segment:

Figure 1.8 – Grouping with two variables

Segment 4 appears to have the smallest standard deviation. This group looks compact. Segment 2 also appears to be compact and it has a high value of revenue.

In Figure 1.9, we will find out the mean and the standard deviation of segment 2:

Figure 1.9 – Segment two mean and standard deviation

As we are analyzing two variables, the centroid of the segment has two coordinates: the online marketing spend and the revenue.

The mean has the following coordinates:

• Online marketing: 5.04
• Revenue: 204.11

In Figure 1.9, the centroid is at these coordinates.

The standard deviation of online marketing is 1.53, and for revenue, it is 76.63.

The limits of the revenue are the black lines. They are 160 and 280. So, segment two is not compact because the majority of points are between 160 and 210 with an outlier close to 280.

When we analyze data with three variables, the mean and the standard deviation are represented by three coordinates. Figure 1.10 shows data with three variables and the segment that each of them belongs to:

Figure 1.10 – Segments with three variables

The mean and standard deviation have three coordinates. For example, for segment three, these are the coordinates:

Figure 1.11 – Mean and standard deviation coordinates with three variables

The standard deviation of revenue is large, 13.73. This means the points are widely scattered from the centroid, 15.8. This segment probably does not give accurate information because the points are not compact.