How to draw the center of mass of a scatter plot in Excel?

Question

I have created a scatter plot in Excel, and I'd like to be able to draw its center of mass (i.e. the point whose coordinates is the average of each series).

I am currently doing it using this trick:

1. I add the center of mass as an additional point to my series, using average function to compute its x- and y-coordinates.
2. I generate my scatter plot using the button "Insert > Scatter plot"
3. I identify and color that point in a different color.

This works fine, and is easy to do when I have a small number of points. But it is much more tedious when I have to deal with a large number of points (e.g. 5000 points).

Therefore, is there any more efficient way to do this? I searched a lot for this, but I was not able to find anything.... :(

Please let me know.

Answer 1

Just add the center mass point as a second series (just one point) with its own formatting and labeling.

In fact, you could just use named ranges for the center mass point, and plot the ranges. There's no need for it to be actually calculated in a spreadsheet cell.

UPDATE
To use the name range:

1. Convert your data to an Excel Table (not absolutely necessary, but simplifies updates and formulas, and generally a good practice).
2. Add a named range for your average X and Y values. Assuming your table is named Table1 (the default) and your X column is named x and your Y column is named y, you'd have two named ranges:
   avg_x =AVERAGE(Table1[x])
avg_y =AVERAGE(Table1[y])
3. Add a series to your chart with the
   X series =sheet1!avg_x
Y series =sheet1!avg_y

Then, whenever your table is updated, your center mass point will automatically update.