promotion image of download ymail app
Promoted
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.

2 Answers

Relevance
  • 10 years ago
    Favorite Answer

    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?

    • Commenter avatarLogin to reply the answers
  • Anonymous
    4 years ago

    1 Open Microsoft Excel. 2 Place one set of data in one column and another set of data in an adjacent column. For example, place one set of data in column A, which will be your X values, and then place another set of data in column B, which will be your Y values. 3 Select the range of values to be included in the scatter plot chart. To select the range, click the first cell to be included then drag your mouse towards the last cell to be included. 4 Go to the "Insert" tab. Click "Scatter" under the "Charts" group menu. 5 Click "Chart Area" for the XY chart. This displays the "Chart Tools," "Design," "Layout," and "Format" tabs specific to the XY chart. 6 Go to the "Design" tab and click the chart style you want to use. 7 Type the title for your chart under "Chart Title." 8 Go to the "Layout" tab and click "Axis Titles." Click the "Primary Horizontal Axis Title" to place a title for the horizontal axis. Click the "Primary Vertical Axis Title" to place a title for the vertical axis. 9 Press "Enter" to exit and show the chart.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.