This guide covers everything you need to know about the Google Sheets DECIMAL function, including its definition, syntax, use cases, and how to use it.
What is the DECIMAL Function? How Does It Work?
The DECIMAL function in Google Sheets returns a decimal representation of a specific number that was initially in another base. Its main purpose is to convert the text representation of a number from any base between 2 and 36, to base 10, which is the decimal system we use daily.
The DECIMAL function operates using two parameters: value and base. The ‘value’ refers to the number you want to convert to decimal. It’s an unsigned value provided in the form of a string. The second parameter, ‘base,’ is the base (or radix) from which the conversion will take place. The base is an integer and can be anywhere from 2 to 36. This wide range of base values allows you to convert numbers from binary (base 2), octal (base 8), hexadecimal (base 16), and other numerical systems into decimal format.
It’s vital to note a few aspects when working with the DECIMAL function. Firstly, the function accepts the value in string format, which should only contain numeric characters. Scientific notation and characters exceeding 255 are not permissible. Secondly, the DECIMAL function only converts to positive integers.
An example of how the DECIMAL function works can be seen with the formula ‘=DECIMAL(101,2)’. In this scenario, the function converts the binary number 101 (in base 2) into a decimal number. The result is 5, as the binary 101 corresponds to 5 in the decimal system.
The DECIMAL function is part of a broader family of Google Sheets functions that offer conversion capabilities between different numerical bases. These include functions like BASE (for converting a decimal number into another base), BIN2DEC (for converting binary to decimal), DEC2BIN (for converting decimal to binary), and HEX2DEC (for converting hexadecimal to decimal), among others. These functions, together with the DECIMAL function, provide you with a comprehensive toolkit to handle and manipulate data across different numerical systems.
DECIMAL Syntax
The syntax and arguments for the function are as follows:
DECIMAL(value, base)
- The ‘value’ argument is the unsigned value to be converted to decimal. This value should be provided as a string.
- The ‘base’ argument represents the base (or radix) to convert the number into. This should be an integer ranging from 2 to 36.
Usage notes related to the syntax and arguments include:
- The ‘value’ argument should only contain numeric characters. Scientific notation is not permitted for this function. The text of a string should be no longer than 255 characters.
- The DECIMAL function only converts to positive integers. Negative values or non-integer numbers are not accepted.
- The ‘base’ argument should be an integer from 2 to 36. This means you can convert numbers from binary (base 2), octal (base 8), decimal (base 10), or hexadecimal (base 16), among others, to decimal format. However, bases outside this range are not accepted.
- The ‘value’ and ‘base’ arguments are mandatory. If either of these arguments is missing, the function will return an error.
- The DECIMAL function returns a decimal number, which is the base 10 equivalent of the provided ‘value’ argument in the specified ‘base’. If the conversion is not possible (for example, if the ‘value’ contains characters not valid in the specified ‘base’), the function will return an error.
- The DECIMAL function is case-insensitive. This means that for bases greater than 10, where alphabetic characters are used to represent digits (for example, ‘A’ for 10, ‘B’ for 11, and so on in hexadecimal), both uppercase and lowercase characters are accepted.
Examples of How to Use the DECIMAL Function
Here are some practical examples of how to use the DECIMAL function in Google Sheets:
Example #1: Converting Binary to Decimal
Let’s say you have a binary number 1101 in cell A1, and you want to convert it to a decimal number. You can use the DECIMAL function to do this. The formula would be:
=DECIMAL(A1, 2)
The “2” in the formula represents the base of the number system you are converting from, which is binary in this case. The result would be 13.
Example #2: Converting Hexadecimal to Decimal
If you have a hexadecimal number, for instance, “A1F” in cell B1, you can also convert it to a decimal number using the DECIMAL function. The formula would be:
=DECIMAL(B1, 16)
The “16” in the formula represents the base of the number system you are converting from, which is hexadecimal in this case. The result would be 2591.
Example #3: Converting Octal to Decimal
Suppose you have an octal number, let’s say 345 in cell C1. You can convert it to a decimal number using the DECIMAL function. The formula would be:
=DECIMAL(C1, 8)
The “8” in the formula represents the base of the number system you are converting from, which is octal in this case. The result would be 229.
Example #4: Converting from Base 5 to Decimal
If you have a number in base 5, for example, 432 in cell D1, you can convert it to a decimal number using the DECIMAL function. The formula would be:
=DECIMAL(D1, 5)
The “5” in the formula represents the base of the number system you are converting from, which is base 5 in this case. The result would be 117.
These examples illustrate how versatile the DECIMAL function can be when you need to convert numbers from different bases to a decimal system.
DECIMAL: Common Mistakes & Problems
When using the DECIMAL function in Google Sheets, there are several common mistakes and problems that users often encounter. These include:
- Incorrect number base: The DECIMAL function in Google Sheets requires a number base between 2 and 36. If you input a number base outside of this range, the function will return an error. Always ensure the number base is within the acceptable range.
- Invalid characters in the text: The DECIMAL function returns an error if the text argument contains characters that are not valid in the given base. For example, in base 2 (binary), only the digits 0 and 1 are valid. If the text includes any other characters, the function will return an error.
- Misunderstanding of the function’s purpose: The DECIMAL function is not designed to convert decimal numbers to another base but rather to convert numbers from another base to decimal. Attempting to use the function in the opposite way will result in incorrect results.
- Incorrect syntax: The syntax for the DECIMAL function is DECIMAL(text, radix). If the arguments are not in the correct order or if there are missing arguments, the function will return an error.
- Over-reliance on automatic type conversion: Google Sheets will sometimes automatically convert numbers to text, but this is not always reliable. It’s best to manually convert numbers to text before using them in the DECIMAL function to avoid errors.
- Using the function with non-integer bases: While some programming languages and software allow the use of non-integer bases, Google Sheets does not. Attempting to use a non-integer base with the DECIMAL function will result in an error.
- Not accounting for case sensitivity: When using bases greater than 10, the DECIMAL function considers upper-case and lower-case letters to be different values. For example, in base 16, ‘A’ represents 10 and ‘a’ represents 36. Be sure to use the correct case for your numbers.
Why Is DECIMAL Not Working? Troubleshooting Common Errors
If you’re using the DECIMAL function in Google Sheets and encountering issues, it’s likely due to a few common errors. This section will guide you through these errors, their causes, and how to resolve them.
#VALUE! Error
Cause: The #VALUE! error usually occurs when the DECIMAL function is provided with non-numerical input. This could be a text string or a cell reference containing text.
Solution: Double-check your function to ensure you only use numerical values or cell references. If you’re using a cell reference, ensure the cell contains a numerical value.
#NUM! Error
Cause: You might encounter the #NUM! error if the radix (the second argument in the DECIMAL function) is less than 2 or greater than 36. The radix must be an integer between 2 and 36, inclusive.
Solution: Make sure the radix you’re using is a number between 2 and 36.
#REF! Error
Cause: The #REF! error appears when the DECIMAL function references a cell that does not exist. This usually happens if columns or rows referenced by your function were deleted.
Solution: Check your function to ensure it’s referencing existing cells. If you’ve recently deleted any columns or rows, adjust your function to reflect the current state of your sheet.
#N/A Error
Cause: This error occurs when Google Sheets can’t find the referenced data. It could be that the data was never there, or it has been moved or deleted.
Solution: Double-check your function and the data it references. Ensure the data exists and is in the correct location. If not, adjust your function or the data location to rectify the issue.
#DIV/0! Error
Cause: The #DIV/0! error is displayed when a number is divided by zero. In the context of the DECIMAL function, this could occur if the radix (base) is zero.
Solution: Ensure your radix is a number other than zero. As mentioned earlier, it should be an integer between 2 and 36.
#ERROR! Error
Cause: The #ERROR! error is a generic error that appears when Google Sheets cannot identify the cause of the problem.
Solution: This might require a bit more troubleshooting. Check for common issues such as the ones listed above. If those don’t seem to be the problem, you may need to head over to Google Sheets Help Center for further assistance.
Using DECIMAL With Other Google Sheets Functions
Combining DECIMAL with other Google Sheets functions can provide powerful results and enable you to carry out more complex calculations and data manipulations. Here are some examples of how you can use the DECIMAL function in conjunction with other functions in Google Sheets.
With SUM
Usage: You can use DECIMAL with the SUM function to sum a range of cells that contain numbers in different bases.
Example: For instance, if you have a range of cells where you have numbers in base 2 (binary) format, you can convert them to base 10 and then sum them up using the following formula:
=SUM(ARRAYFORMULA(DECIMAL(A2:A10, 2)))
This formula will convert all numbers in cells A2 through A10 from binary to decimal and then sum them up.
With AVERAGE
Usage: The DECIMAL function can be combined with the AVERAGE function to calculate the average of a range of cells containing numbers in different bases.
Example: If you have a range of cells where you have numbers in base 16 (hexadecimal), you can convert them to base 10 and then compute their average using the following formula:
=AVERAGE(ARRAYFORMULA(DECIMAL(B2:B10, 16)))
This formula will convert all numbers in cells B2 through B10 from hexadecimal to decimal and then compute their average.
With IF
Usage: The DECIMAL function can be used with the IF function to perform conditional calculations based on the values of cells in different bases.
Example: Let’s say you want to convert a cell from base 8 (octal) to base 10 only if the cell value in base 8 is greater than 10. You can achieve this by using the following formula:
=IF(A2>10, DECIMAL(A2, 8), A2)
This formula will check if the value in cell A2 (in base 8) is greater than 10. If it is, it will convert the value to base 10 using the DECIMAL function. Otherwise, it will leave the value as it is.
With CONCATENATE
Usage: You can use the DECIMAL function with the CONCATENATE function to create a string that includes a number in base 10 converted from a different base.
Example: If you have a number in base 5 in cell A2 and you want to create a string that says “The number in base 10 is X”, where X is the number converted to base 10, you can use the following formula:
=CONCATENATE(“The number in base 10 is “, DECIMAL(A2, 5))
This formula will convert the number in cell A2 from base 5 to base 10 and then concatenate it with the given string.
For more details on the DECIMAL function, check out the official documentation at the Google Docs Editors Help Center.
