1

I have a x and y set of data I am trying to filter for a graph. The problem with my data is there are too many points, and I would like to filter out a significant amount of the data. In my case, I have data points corresponding to 5 second increments, but I would like to filter the data to only include points every 20 minutes.

To get the filtered x-axis data, I am using the following IF/ELSE statement:

IF(MOD(K6/20,1)=0,L6,0) Note: K6 is unfiltered x-axis data.

This statement either sets the value in the column to zero if the value isn't a increment of 20, or the value is set equal to actual time value.

For the y-axis I have been using the following code to filter the data. The y-axis values are determined based on the filtered x-axis values.

=IF($U6 = 0,"",M6) Note: U6 is filtered x-axis data, and M6 is unfiltered y-axis data.

This statement either leaves the y-axis cell blank if the time value isn't a increment of 20, or the value is set equal to the actual y-axis value.

Both of my statements work, but the problem is Excel associates a blank cell with a value of 0 on a graph. I have also tried "NA" and NULL in the IF/ELSE statement, but I always get the same association to 0 in the graph. Does anyone know how to fix this?

Thanks

3
  • 1
    Use a filter (Data -> Sort & Filter -> Filter) to hide the rows you don't want to appear (i.e. those whose value is blank in your formula column) – cybernetic.nomad Aug 5 '19 at 21:46
  • Are you using the static value NA or the formula NA()? Excel should ignore #N/A, which are the result of NA(). Other values may be interpreted as 0 and plotted. – dav Aug 6 '19 at 19:03
  • Thank you, the filter data worked perfectly. Just a note so other people realize, if you have your graph in the same area as your data, move the graph before filtering the data. Otherwise, when you filter the data the graph will shrink with the removal of the rows in excel. – Grady F. Mathews Iv Aug 7 '19 at 13:55
1

I made a spreadsheet that demonstrates a neat way of doing this. You use the INDEX() function to retrieve every 20th value.

So my formula for filtering out data reads like this:

=IFERROR(INDEX(X_Data__Raw_, (Value_No * Filter_Every), 1), NA())

Where

  • Value_No is a column of 1, 2, 3, ... next to the X_Data__Raw_column
  • Filter_Every is 20

Here is what each part is doing:

  • The first argument to INDEX() is the column of data, X_Data__Raw_
  • The second is the row which will be returned, in this case 1, 2, 3, ... times 20
  • The third is the column, which will always be 1
  • the IFERROR() function catches all the times that the row argument is too big, and returns NA(), which is then ignored by the chart

I used FormulaChop (Full disclosure: I wrote FormulaChop) to generate the formula above. Here is a screenshot of the output. Here is a link to the spreadsheet.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.