Of course, since the chart series update automatically, the highlighted point will change once you type a different name in the Target Month cell (E2). As the Series Y value, select the dependent In our case, it's G2 (Items Sold).Īs the result, a data point in a different color (orange in our case) will appear among the existing data points, and that is the point you are looking for:.As the Series X value, select the independent variable for your data point.Enter a meaningful name in the Series name box, e.g.In the Edit Series window, do the following:.In the Select Data Source dialogue box, click the Add button.Right-click any axis in your chart and click Select Data….For this, we will have to add a new data series to our Excel scatter chart: With the source data ready, let's create a data point spotter. In G2, pull the advertising cost for the target month by using this formula:Īt this point, your data should look similar to this:.In F2, insert the following VLOOKUP formula to extract the number of the sold items for the target month:.It is important that you enter the label exactly as it appears in your source table. In our case, let it be the month of May in cell E2. Enter the point's text label in a separate cell.That means we need to get the x ( Advertising) and y ( Items sold) values for the data point of interest. So, we need to figure out a way to find, highlight and, optionally, label only a specific data point.Įxtract x and y values for the data pointĪs you know, in a scatter plot, the correlated variables are combined into a single data point. But our scatter graph has quite a lot of points and the labels would only clutter it. If we had fewer points, we could simply label each point by name. Now, you want to be able to quickly find the data point for a particular month. Supposing, you have two columns of related numeric data, say monthly advertising costs and sales, and you have already created a scatter plot that shows the correlation between these data:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |