Scatterplot - displaying overlapping data?

**Lis7** · 11-04-2012, 10:54 AM

Am creating scatterplots and some of the data overlaps. I tried using RANDBETWEEN, and it does spread the points, but the drawback is that the distances vary unevenly and the values change each time Excel recalculates anything on the worksheet, so the chart changes too.

Looking for advice on how to set this up so that Excel will check to see if there are identical data points within a range of cells in Series B, and than add or subtract a specific amount (.05) from the original Series B value to create a new calculated result in the third column (example below).

It needs to automatically check to see how many cells have the identical result:

One value: don't change the value.
Two identical values: subtract .05 from the original value for one cell, and leave the original value unchanged for the second cell.
Three identical values: subtract .05 from the original value for one cell, leave one unchanged, and add .05 to the third identical value.
And so on... there could be up to 10 - 20 identical values in Series B.

Series A-- Series B-- Series B Calculated Result
1-- 1-- 0.95
1-- 1-- 1.00
1-- 3-- 3.00
4-- 4-- 3.90
4-- 4-- 3.95
4-- 4-- 4.00
4-- 4-- 4.05
4-- 4-- 4.10

Thank you.

**mrice** · 11-04-2012, 03:03 PM

Try this - the third column should be blank when you start it. Note that the data is sorted by column 2 as a first step to make it a bit simpler to deal with. This shoudl not affect the scatter plot.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.