Systematic elimination of data
Posted by Michael on November 02, 2001 12:02 PM
I'm working on a spreadsheet that has far too much data. I have 16,000 data points and I would like to reduce this to about 1,000 data points. I need a fast method for eliminating 15 data points and then keeping one, eliminating 15 data points, and keeping one, etc., etc. I tried to set up an IF, THEN statement by dividing each number by 16, using the ROUND command to make the number a whole number and then subracting the original, non-rounded number from it. If the value was not equal to zero, nothing was added to the spread sheet. If the number was equal to zero, the data point was left. The problem with this method was that the cells were not really empty and thus I could not compress the data from 16,000 separate cells down to 1,000. When I tried to plot the data, all the blank cells showed up as zeros. Does anyone know of a simple technique to reduce the data set quickly?
Posted by Todd on November 02, 2001 12:14 PM
make sure that:
the data range of your graph doesn't contain the empty cells. you can do a sort to sort out the blank cells, then fix the data range.
Posted by Michael on November 02, 2001 12:38 PM
Just looking for a simple technique to delete 15 cells, leave one, delete another 15 cells. I tried the SORT and FILTER options but I don't know how they work or how to specify the action I'd like taken. If anyone can provide help, that would be great!
Posted by Todd on November 02, 2001 12:48 PM
i have to leave for the wknd soon so last post..
i think you're on the right track..
do alt-d-s after selecting the range you want to sort. first sort ascending on the column that has some blank entries and some not. so all the blank cells are together. delete them. then sort by the column that has the order numbers.. 16, 32, etc.
then use this range in your graph.
good luck..
Posted by Allen Simonsen on November 03, 2001 1:42 AM
If you want to keep row 1 and every 15th row thereafter and delete the other rows (perhaps this is not what you want?) :-
1. Insert a column (say column A)
2. In A2 put =IF(MOD(ROW(),16)=0,1,"")
3. Fill down to the end of your data
4. Select column A and go to Edit>GoTo>Special>Formulas>Text>OK
5. Go to Edit>Delete>EntireRow>OK
6. Delete column A
If you don't want to delete any rows, then after step 3 :-
Select column A, Copy>PasteSpecial>Values, Sort by column A, revise the data range ref to match the rows that contain "1" in column A, delete column A.
