Miles per hour (mph) and kilometers per hour (km/h) are units of speed that measure how fast an object is moving. To convert from mph to km/h, we need to multiply the mph value by a conversion factor of 1.60934. This is because one mile is equal to 1.60934 kilometers. For example, if a car is moving at 60 mph, its speed in km/h is 60 x 1.60934 = 96.56 km/h.
To convert mph to km/h in Excel, we can use a simple formula that multiplies the mph value by 1.60934. For example, if we have the mph values in column A, we can enter the following formula in column B to get the km/h values:
=A2*1.60934
We can then copy and paste the formula to the rest of the cells in column B. Alternatively, we can use the fill handle to drag the formula down to the desired range.
Explanation
The formula =A2*1.60934 works by taking the value in cell A2, which is the mph value, and multiplying it by 1.60934, which is the conversion factor from miles to kilometers. The result is the km/h value, which is displayed in cell B2. The formula can be applied to any cell that contains a mph value, and it will return the corresponding km/h value.
To make the formula more readable and flexible, we can use a named range to store the conversion factor. A named range is a way of assigning a name to a cell or a range of cells, so that we can refer to them by name instead of by address. To create a named range, we can follow these steps:
- Select the cell that contains the conversion factor, which is 1.60934 in this case.
- Go to the Formulas tab and click on Define Name in the Defined Names group.
- In the New Name dialog box, enter a name for the conversion factor, such as “Mile_to_Km”.
- Click OK to create the named range.
Now, we can use the name “Mile_to_Km” instead of the value 1.60934 in our formula. For example, we can enter the following formula in column B to get the km/h values:
=A2*Mile_to_Km
This formula is easier to understand and maintain, as it clearly shows the meaning and source of the conversion factor. We can also change the value of the conversion factor in one place, and it will automatically update all the formulas that use it.
Example
To illustrate how to use the formula to convert mph to km/h, let us consider a scenario where we have a table of data that shows the speed of different cars in mph. We want to add a column that shows the speed of the cars in km/h. The table looks like this:
| Car | Speed (mph) |
|---|---|
| A | 55 |
| B | 65 |
| C | 75 |
| D | 85 |
| E | 95 |
To add the km/h column, we can follow these steps:
- Select the cell that contains the conversion factor, which is 1.60934 in this case, and create a named range called “Mile_to_Km” as explained above.
- In cell C2, enter the formula
=B2*Mile_to_Kmto get the km/h value for car A. - Copy and paste the formula to the rest of the cells in column C, or use the fill handle to drag the formula down to the desired range.
The table will now look like this:
| Car | Speed (mph) | Speed (km/h) |
|---|---|---|
| A | 55 | 88.51 |
| B | 65 | 104.61 |
| C | 75 | 120.70 |
| D | 85 | 136.80 |
| E | 95 | 152.89 |
We can see that the formula has successfully converted the mph values to km/h values for each car.
Other Approaches
Besides using a formula, there are other ways to convert mph to km/h in Excel. Here are some of them:
- Using a conversion table: We can create a table that lists the mph values and their corresponding km/h values, and then use the VLOOKUP or INDEX/MATCH functions to look up the km/h value for a given mph value. For example, if we have the following table in cells F2:G11:
| mph | km/h |
|---|---|
| 0 | 0 |
| 10 | 16.09 |
| 20 | 32.19 |
| 30 | 48.28 |
| 40 | 64.37 |
| 50 | 80.47 |
| 60 | 96.56 |
| 70 | 112.65 |
| 80 | 128.75 |
| 90 | 144.84 |
We can enter the following formula in cell C2 to get the km/h value for car A:
=VLOOKUP(B2,F2:G11,2,0)
This formula uses the VLOOKUP function to search for the mph value in cell B2 in the first column of the table in F2:G11, and return the km/h value from the second column of the table. The last argument, 0, means that we want an exact match for the mph value. We can then copy and paste the formula to the rest of the cells in column C.
Alternatively, we can use the INDEX/MATCH functions to achieve the same result. For example, we can enter the following formula in cell C2:
=INDEX(G2:G11,MATCH(B2,F2:F11,0))
This formula uses the INDEX function to return the value from the range G2:G11 at the position given by the MATCH function. The MATCH function searches for the mph value in cell B2 in the range F2:F11, and returns its relative position. The last argument, 0, means that we want an exact match for the mph value. We can then copy and paste the formula to the rest of the cells in column C.
- Using a conversion function: We can create a custom function in Excel that takes a mph value as an argument and returns the km/h value. To do this, we need to use the Visual Basic for Applications (VBA) editor, which is a programming environment that allows us to write macros and functions in Excel. To create a custom function, we can follow these steps:
- Press Alt + F11 to open the VBA editor.
- In the Project Explorer window, right-click on the workbook name and select Insert > Module.
- In the code window, type the following code:
Function MPHtoKMH(mph As Double) As Double
'This function converts miles per hour to kilometers per hour
MPHtoKMH = mph * 1.60934
End Function
- Save the workbook as a macro-enabled workbook with the extension .xlsm.
- Close the VBA editor and return to Excel.
Now, we can use the custom function in our worksheet. For example, we can enter the following formula in cell C2 to get the km/h value for car A:
=MPHtoKMH(B2)
This formula calls the custom function MPHtoKMH and passes the mph value in cell B2 as an argument. The function returns the km/h value, which is displayed in cell C2. We can then copy and paste the formula to the rest of the cells in column C.
