Overview
The rate of change function allows you to calculate the rate of change between two different columns of data. This is a powerful way to calculate quantities based on derivatives, like velocity (dx/dt), force (dp/dt), concentration gradients (dC/dx), and more.
Adding a Rate of Change column
Click the three vertical buttons on the right of the column heading
Select Insert Column Right
Give the new column a name, variable, and units
Click the three vertical buttons on the right of the column heading again
Select Change Column Formula. This will open the Column Formula Calculator
Press the Rate of Change button in the calculator
7. This will fill the formula bar with a template for calculating the rate of change. The [y] will be filled by the column you are taking the rate of change of, and the [x] will be filled by the column you are taking the rate of change with respect to.
8. Click the column that you wish to take the rate of change of [y] from the list of available columns in the column menu on the right side of the Column Formula Calculator.
Note: The Column Formula calculator only has access to columns that are to the left of the column you are calculating). This will replace [y] with the name of a column.
Note also that the red outline indicated that this formula is not a valid formula.
9. Click the column that you wish to take the rate of change with respect to [x] from the list of available columns in the column menu.
10. Press Submit. This will add a calculated column that is the rate of change you selected. You can adjust the formatting of the numbers from the Column Settings in the three dots menu for the column.
Note: You can also create column formulas that involve functions of rate of change using the calculator, such as 2*rateOfChange, or ln(rateOfChange), just as you would for any other column calculation.
Here is an animated gif showing the entire process of adding a calculated column to determine the velocity from some position data:
Details of the algorithm used to calculate the rate of change
When designing this algorithm, we wanted to use something that would be both powerful—generating a nice rate of change function for a wide variety of data, and easy to understand and familiar to students who are just learning how to calculate the rate of change.
Our algorithm begins by removing any rows where one or both of the values are missing in the selected columns. This allows us to calculate a rate of change for points where there is a gap in the data, so long as there are complete data points on either side of a particular point.
For each of the data points, our algorithm generates a weighted average of the slope of its surrounding points, subject to these four cases:
If either of the neighboring data points is missing, i.e. this data point is at the beginning or end of the table, the algorithm does not calculate a rate of change and leaves the cell empty.
If there is only one set of neighboring data points, i.e. the data point is next to the first or last data point in the table, the uses the slope between its neighboring points to calculate the rate of change at the middle point:
If there are only two sets of neighboring data points, the algorithm computes a weighted average of the slopes, with the slope of the near set of points being weighted more heavily (the slope of nearest neighbors makes up 2/3 or 66% of the average with the 2nd neighbors making up the other 1/3) :
If there are three or more sets of neighboring data points, the algorithm computes a weighted average of the slopes, again weighting the nearest points most heavily (the slope between the nearest neighbors makes up 3/6 or 50% of the average, the second neighbors make up 2/6 or 33% of the average, and the third neighbors make up 1/6 or 16% of the average.
These weighted averages have the effect of smoothing out the rate of change function, which is particularly helpful for noisy data.