Scatter Plot X,Y Coordinate Number of Occurrences

spochedly

New Member
Joined
Aug 20, 2018
Messages
4
I am looking to build a scatter plot to illustrate how many times an X,Y coordinate appears. So, if I have 15 occurrences of 2,3 then I would like to see 15 points at 2,3 on my chart. If I have 3 occurrences of 2,1 XY corrdinate, I want to see 3 points right there.

I need this to be a scatter plot to best illustrate the distribution of data.

Please/thanks in advance!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
How will I know that there are 15 points at 2,3?

Maybe instead of stacking a lot of points on top of each other, where you can't see them, you might make a marker proportional to the number of times the X,Y coordinates appear. That would be a bubble chart.

So we need to count the occurrence of XY pairs. In my sample here, I have X in B3:B28 and Y in C3:C28. I have listed the possible combinations of X and Y in E3:F18. In G3 (filled down to G18) I have the formula

=COUNTIFS($B$3:$B$28,E3,$C$3:$C$28,F3)

So I have a count for each combination of X and Y.

And I made a bubble chart from this data.

 

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,953
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top