Anonymous
Anonymous asked in Computers & InternetSoftware · 10 years ago

# What is the best way to reduce quantity of data points for excel scatter plot?

I am working with data that I gathered during an X-ray diffraction lab. The xrd outputs data with 2 theta angle and intensity. There are 5000+ data points for each of the 15 different graphs I need to generate. What is a good way to statistically reduce the number of data points in excel? Even a macro for vba would probably work by using a counter but I think others may know better ways. What do you suggest that I do to reduce the data?

An idea of what the graphs look like can be seen by searching in google with the phrase "xrd pattern" with images selected. Also, sites like http://www.hkbu.edu.hk/~csar/phase_identification.... have images.

Relevance
• 10 years ago

If you just want to use only every nth data point this works well:

http://pubs.logicalexpressions.com/Pub0009/LPMArti...

A method that works but not useful for a lot of data:

http://www.ehow.com/way_5880244_excel-point-reduct...

If your data starts in Row 2, you can copy this formula and drag down. It'll give you the average of every 10 data points, and blank for the rest. Then, copy and paste as values, select special (F5), blank cells, delete, shift up, and you have the data "condensed".

=IF(MOD(ROW()-1,10)=0,IF(ROW()>=10,AVERAGE(INDIRECT("A"&ROW()&":A"&ROW()-9)),""),"")

• Raul4 years agoReport

What if I wanted to find the average of every 2 points? How would I modify that excel formula?