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?