Explanation
In this example, the goal is to convert the numbers in column B to either 1 or 0, depending on whether the number is greater than zero or not. If the number in column B is greater than zero, the result should be 1. If the number in column B is less than or equal to zero, the result should be zero.
With the IF function
One way to handle this problem is with the IF function . In the example shown, the formula in D5, copied down, is:
=IF(B5>0,1,0)
The logic is simple: when the number B5 is greater than zero, IF returns 1; otherwise, IF returns 0. The logical test inside IF can be adjusted to apply different logic if needed.
With Boolean logic
Another option for solving this problem is to use Boolean logic directly like this:
=--(B5>0)
Here, the expression B5>0 evaluates to either TRUE or FALSE. The double negative (–) is used to convert the TRUE and FALSE into the numeric equivalents, 1 and 0. This conversion can also be handled with the N function:
=N(B5>0)
Video: Boolean algebra in Excel
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 .
