How to Calculate Average Rate of Change in Excel

Average rate of change can be calculated as:

Average Rate of Change = (y₂ – y₁) / (x₂ – x₁)

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.

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.

The following tutorials explain how to perform other common tasks in Excel: