Coincident (or overlapping) points

P.H. · May 7th, 2002, 07:34 PM

When more than one point is identical on a scatter-plot, this can't be seen by just looking at the chart. As a result, the use of the chart is decreased--especially for regression analysis where this information is important. I can’t select every point to check it individually or print a correct chart. Other statistical programs clearly show coincident (or overlapping) points by increasing the size of the point, adding 'leaves' to the point (each leaf representing one overlap) or showing the number of overlaps with a written number by the point.

Is there an add-in, a function, or some other way with Excel of showing graphically how many point are overlapping at each point?

NateO · May 7th, 2002, 08:01 PM

You may want to look at the following. It's based on a single criteria:

http://msdn.microsoft.com/library/de...ml/o2k0601.asp

You can sum, graph, etc... column c (in their example) or customize the function as you see fit. if you are testing multiple criteria, you'll need an array formula, post back in that case....

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-07 19:10 ]

P.H. · May 7th, 2002, 10:21 PM

Yes, this looks interesting although extremely complicated for charts from a 30- column spreadsheet.

Worse still, I WILL be using multiple criteria because each point is x,y. Not just x, or just y. So, if you can advise me on this I'd really appreciate it.

P.H.

NateO · May 8th, 2002, 10:49 AM

Not necessarily following your spreadsheet, but let me give this a whack. If each row represents a point, and you have thirty columns (let's pretend a1:ad1) you could put the following array formula in ae1 to count duplicate entries (doesn't count the original) for that row. Copy the following formula, paste it in your formula bar and press ctrl-shift-enter at the same time:

=COUNT(A1:AD1)-SUM(1/COUNTIF(A1:AD1,A1:AD1))

the formula should be surrounded by { } at this point (in formula bar). You can now copy this downward. If I have the columns and rows jumbled, transpose the ranges in the formula.

The deal though is that it strictly counts duplicates, it doesn't care which value it's duplicating. This can't be what you want, but I'm confused about your array/table structure.

Cheers, Nate

[ This Message was edited by: NateO on 2002-05-08 10:34 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 7th, 2002, 07:34 PM	#1
P.H. New Member Join Date: May 2002 Posts: 4	When more than one point is identical on a scatter-plot, this can't be seen by just looking at the chart. As a result, the use of the chart is decreased--especially for regression analysis where this information is important. I can’t select every point to check it individually or print a correct chart. Other statistical programs clearly show coincident (or overlapping) points by increasing the size of the point, adding 'leaves' to the point (each leaf representing one overlap) or showing the number of overlaps with a written number by the point. Is there an add-in, a function, or some other way with Excel of showing graphically how many point are overlapping at each point?

May 7th, 2002, 08:01 PM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	You may want to look at the following. It's based on a single criteria: http://msdn.microsoft.com/library/de...ml/o2k0601.asp You can sum, graph, etc... column c (in their example) or customize the function as you see fit. if you are testing multiple criteria, you'll need an array formula, post back in that case.... _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-05-07 19:10 ]

May 7th, 2002, 10:21 PM	#3
P.H. New Member Join Date: May 2002 Posts: 4	Yes, this looks interesting although extremely complicated for charts from a 30- column spreadsheet. Worse still, I WILL be using multiple criteria because each point is x,y. Not just x, or just y. So, if you can advise me on this I'd really appreciate it. P.H.

May 8th, 2002, 10:49 AM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Not necessarily following your spreadsheet, but let me give this a whack. If each row represents a point, and you have thirty columns (let's pretend a1:ad1) you could put the following array formula in ae1 to count duplicate entries (doesn't count the original) for that row. Copy the following formula, paste it in your formula bar and press ctrl-shift-enter at the same time: =COUNT(A1:AD1)-SUM(1/COUNTIF(A1:AD1,A1:AD1)) the formula should be surrounded by { } at this point (in formula bar). You can now copy this downward. If I have the columns and rows jumbled, transpose the ranges in the formula. The deal though is that it strictly counts duplicates, it doesn't care which value it's duplicating. This can't be what you want, but I'm confused about your array/table structure. Cheers, Nate [ This Message was edited by: NateO on 2002-05-08 10:34 ]