Table of Contents
To calculate Average Rate of Change in Excel, take the change in the value of the function over the interval, divided by the change in the independent variable over the same interval. This will give an exact rate of change for that interval. You can get an average rate of change for a whole function by taking the sum of all the individual rates of change and dividing by the total number of intervals.
Average rate of change can be calculated as:
Average Rate of Change = (y2 – y1) / (x2 – x1)
The following example shows how to calculate average rate of change in Excel by using a simple formula.
Example: Calculate Average Rate of Change in Excel
Suppose we have the following column of times and distances (in miles) for some marathon runner:
Suppose we would like to calculate the average rate of change (i.e. the average speed) for this particular runner.
To do so, we can type the following formula into cell D2:
=(B8-B2)/((A8-A2)*24)
The following screenshot shows how to use this formula in practice:
The formula returns a time instead of a number.
To display a number instead, select cell D2 and then click the Number Format dropdown menu from the Home tab, then click Number:
The value in cell D2 will now be displayed as a number:
We can see that the average rate of change, i.e. the average speed of this marathon runner, is 8.68 miles per hour.
How This Formula Works
Recall the formula that we used to calculate the average rate of change:
=(B8-B2)/((A8-A2)*24)
Here is how this formula works:
First, we subtract the starting distance (B2) from the ending distance (B8) to get the total distance covered.
Then, we subtract the starting time (A2) from the ending time (A8) to get the total time elapsed.
Then, we multiply the time elapsed by 24 to ensure that we’re working with hours instead of days.
Lastly, we divide the total time elapsed by the total distance covered to get the average speed (in miles per hour) of the runner.