Explanation
This formula relies on the CONVERT function , which can convert a number in one measurement system to another. To perform the conversion, CONVERT relies on “from” and “to” units entered as text. As long as the units specify valid options, CONVERT will automatically perform a conversion and return a numeric result.
To convert pounds to kilograms, the formula used is in C5, copied down, is:
=CONVERT(B5,"lbm","kg")
Note: CONVERT is case-sensitive, so the text values used for units must match exactly.
Kilograms to pounds
To convert from kilograms to pounds, the formula would be:
=CONVERT(B5,"kg","lbm")
Pounds to Stones
To convert pounds to kilograms, the formula used in D5, copied down, is
=CONVERT(B5,"lbm","stone")
Table set-up
The table in the example shown uses formulas to make it easy to change the staring point and range of the values in column B. The formula in B5 simply points to G4:
=G4
The formula in G5, copied down, is:
=B5+$G$5
When values in G4 or G5 are changed, the values in column B update dynamically.
Other conversions
You can use CONVERT to convert weight, distance, time, pressure, force, energy, power, magnetism, temperature, liquid, and volume. Unit strings must be valid and in the proper case. This page shows available options in each category .
Explanation
In Excel, you can’t easily create formulas that skip columns following a certain pattern, because the references in the formula will automatically change to maintain the relationship between the original source cell and the new target cell. However, with a little work, it’s possible to construct formula references that will follow a specific pattern using the OFFSET function.
The OFFSET function is designed to create references by using “offsets” from a starting cell. In the example shown, the starting cell is C5, provided to OFFSET as an absolute reference so it won’t change as the formula is copied:
=OFFSET($C$5
For the rows argument, we provide zero, since we want to stay in the same row. For the columns argument, we use a sub-formula to calculate the required offset value:
(COLUMN(A8)*3)-1
We use A8 inside COLUMN to return 1 (since A is the first column), then multiply by n (which is 3 in this case) to get 3.
As the formula is copied across the row to the right, the value returned by COLUMN increments by 1, which is what creates the “nth pattern”.
Starting at 1
If you want to start copying at the first value, you can adjust the formula like this:
=OFFSET($C$5,0,(COLUMN(A11)-1)*3)
By subtracting 1, we force a column offset of zero in the first formula.
Copy to rows instead of columns
To copy from columns into rows, you can modify the formula like this:
=OFFSET($C$5,0,(ROW(C1)*3)-1)
Here, the COLUMN function has been replaced with the ROW function , and a reference to the first row in the column, so that incrementing works correctly as the formula is copied down into multiple rows.
