Programmers and data handlers often use complex tools for simple number conversions. However, Excel already offers some great options to help convert numbers. One of those options is the Excel BASE function. And we will discuss everything there is to know about this exceptional function in this guide.
The BASE function is a wonderful tool for analysts, programmers, and system administrators. It is a simple function that allows the user to convert a number from one base to a different base. There is no better option for converting integers to binary, octal, hexadecimal numbers, etc.
We will explain everything you need to know about this BASE function in this guide. Besides showing how to use this function with important examples, we will also discuss solutions to the problems and errors you may face while using this function. For easier learning, we will include visual demonstrations as well.
Let’s begin the BASE journey!
Contents
What is the BASE Function in Excel?
In Excel, the BASE function belongs to the Mathematical and Trigonometric categories. You can access it using a few techniques. We will show you all the ways so that if you are a beginner or an expert user, you can pick up your preferred method based on what seems easy.
Microsoft first added this BASE function in Excel 2013. Every version of Excel since then has come with this function. If your current Excel program is older than Excel 2013, upgrade it to a later version. Then you will gain access to this special function and many other things.
Now let’s see the techniques of using the Excel BASE function. First, you must select a cell where you want to add the formula. Then follow any of the methods we are explaining below.
1. You can go to the Formulas ribbon and find and click the Math & Trig option in the Function Library. Then from the drop-down list, click on the BASE option.
2. You can also go to the Formulas tab and then find and click the Insert Function option in the Function Library.
A mini Insert Function options window will pop open. Under the Search for a function option, write BASE in the box and click the OK button. Then you will find a Select a function section in the same window below. It shows the search result. You should find the BASE function in the list.
Click on that BASE option, and finally, click OK.
Following any technique from the two we have explained above will take you to the Function Arguments window. Here, you will need to input your values for each argument. Afterward, clicking the OK button will complete the process and insert your input values within the BASE formula in the cell you selected initially.
3. The third way is pretty simple; you write down the formula and arguments yourself in your selected cell. To do it, select your cell first and then start writing the formula by inputting an equal sign first, followed by the function name BASE. Then input an open parenthesis. Now Excel will show you hints for each argument below.
For a seasoned Excel user, the third way may be the easiest. Any new user may follow this process as well. But understanding the syntax of the function is necessary before taking this approach. For this reason, we will do a syntax breakdown of the BASE function in the next section.
Keep on reading to learn more about this important mathematical function in Excel!
Excel BASE Function: Syntax Breakdown
Before doing anything, understanding the syntaxes of the functions is the most important thing for any Excel user. It is no different for the Excel BASE function, either. Therefore, we will explain the syntax of this function in this part of the article.
The BASE function has three arguments: two are mandatory, and one is optional.
The formula for the function is =BASE(number,radix,[min_length]).
Here,
- BASE : The function.
- number : The first mandatory argument. It is the number that you want to convert to a text representation. It should always be greater than or equal to 0 (zero) and less than 2^53. The number always has to be an integer.
- radix : The second mandatory argument. It is also known as base radix or simply base. After taking the numeric value from the number argument in the first step, the function will follow the value of radix as the base. This value should always be greater than or equal to 2 and less than 36. Like the previous argument, it must be an integer as well.
- [min_length] : An optional argument. It refers to the character length of the converted string. Most users don’t define this argument in the formula and let the function put out the default length for a converted string. However, when a user needs to use it, the value always has to be a number, and it must be an integer.
Suppose the minimum length of the string turns out to be bigger than the calculated value. In that case, the BASE function will add zeros in front of the result until it meets the minimum length criteria. On the other hand, if the defined min_length has a value of the string smaller than a calculated value, the function will ignore the min_length argument and show the result directly.
In upcoming sections, we will explain and demonstrate how this BASE function works as well as some uses of it.
How Does the BASE Function Work?
As you already know, the job of the Excel BASE function is to convert numbers to a text representation based on the specified radix base value. It uses all three arguments during operation even if an Excel user does not define the optional argument. And it works in three simple steps.
1. First, the function takes the numeric value from the number argument.
2. Then it looks for the number declared in the radix argument. The function takes this value as the base of the specified number and converts it to the mentioned radix base.
3. Finally, the function looks for any value mentioned in the min_length argument. If nothing is specified, the BASE function will stop the process here and show the calculated value in its text representation.
We will use a short demonstration to explain the process further. We will use the numeric value 18 for the number argument, 2 for the radix argument, and 8 for the min_length argument. Here, the last argument is optional. But for demonstration purposes, we are including it in the calculation anyway.
Using the mentioned arguments, we get the BASE function formula =BASE(18,2,8). As the returned text representation of the mentioned number 18 on base 2 with the minimum length of 8 characters, we have gotten 00010010.
Furthermore, we will use the Cell Reference technique below to understand the function better. See the image here.
And we got the same result when we manually wrote all the values.
If the min_length had been excluded from the arguments for this demonstration, the result would have been 10010. The leading zeros to match the minimum length of the string (8 characters, in this case) would not have been included in the result.
How to Use Excel BASE Function?
In this section of the guide, we will show three examples of using the BASE function in Excel. Any number between 2 and 36 is acceptable for the radix value while using the BASE function. However, we will be doing three conversions as a demonstration in this section; a binary conversion (a 2 base), an octal conversion (an 8 base), and a hexadecimal conversion (a 16 base) of some numbers.
The dataset before calculation looks like below.
We will do Cell Referencing for the numbers and input the values manually for other arguments. And after we complete all three conversions, we will also share the converted numbers’ final look.
Let’s begin.
Binary Conversion
Write down the formula. Select the cell where your number is after inputting open parenthesis. Then put a comma.
Now put “2” as the radix value for the binary conversion. Close the parenthesis and press the Enter key.
You will see that your number has been converted to a binary text representation. Fill the column with the formula to calculate and convert the rest of the values with a 2 radix.
Here, we did not mention the min_length argument in the formula. If you would like to have a minimum string length, be sure to include it in your calculation.
For example, now we will write “12” as the min_length in the same formula to show the process.
After hitting Enter, we got all numbers converted into binary representation again. But this time, they all have a minimum character length to maintain, which is 12 characters.
The zeros in the front filled up the requirement of the min_length. That is how it works!
Octal Conversion
The Binary and Octal conversion follow a similar process. The only difference lies in the radix value here. In binary, the base is 2, whereas, in octal, it is 8. Let’s see below.
Write the formula for this conversion. Select the cell with the number for the number argument, put a comma, and then write 8 for the octal specification. Then close the parenthesis and hit Enter.
You should see that the number has been converted to an octal base text representation. Fill up the column using the Fill Handle to convert the rest of the numbers.
To demonstrate the change when min_length is defined, we then added 10 for this argument.
As a result, the octal conversions had 10 characters, with leading zeros filling up the string length requirement.
Hexadecimal Conversion
For the hexadecimal conversion, we will use a base or radix of 16. Other process stays the same as before.
Just put 16 as the base and see the magic happen.
The image below shows you the converted hexadecimal text representation of numbers in the dataset.
And when we put 6 as the minimum character length value, we got the result below.
Pretty easy!
We showed you the dataset before doing this conversion operation with the Excel BASE function. Now see the picture below for a complete final look.
You can compare before and after pictures to understand the BASE function further.
Why is the BASE Function Not Working Correctly?
As an Excel user, everyone has been in a situation when a function was not working as it should have been. The BASE function may also give you hard times. Worry not because we will discuss all the possible problems you can face while using the BASE function.
Usually, when something is not working correctly, an Excel user recognizes it after seeing discrepancies in the results. There may be error messages or some weird-looking elements in the result. It is the same in the BASE function as well. In this section, we will talk about the solutions to five different problems this function will possibly throw at you.
Reason 1: Leading Zeros in the Result
We take it you have been reading this guide attentively. So you already know that in BASE function results, the leading zeros come from minimum character length arguments. The function adds zeros in front of the calculated values to fulfill the criteria of a specific minimum string length. Notice the image below.
To eliminate the leading zeros, remove the last argument from the formula.
And you will see that the zeros from the front are now gone!
Easy-peasy!
Reason 2: Excel BASE Function Returning #VALUE! Error
Getting a #VALUE! error while using the BASE function in Excel is also a common occurrence. The reason behind this error is using a non-numeric value. For instance, take a look at the picture below.
Here, the number is not in numeric form. As a result, Excel has returned a #VALUE! error.
To remedy this problem, write the number in numeric form.
And the error will go away, giving you the calculated result you want.
That’s all!
Reason 3: The #NUM! Error in the BASE Function
Another error you can get during a BASE calculation is the #NUM! error. In fact, this is the most common error reported by users. And it is tough to figure out why you are getting this error unless you know some important conditions of Excel’s BASE function.
As you already know, there are three arguments in the formula for this BASE function; number, radix, and min_length. All of the arguments have to be integers. Yet, you cannot put just any number in these arguments. There are some minimum and maximum limitations to the numbers you can use.
- For the number argument, you cannot use a number less than or equal to 0 (zero) and greater than or equal to 2^53. In other words, the integer for the number argument always has to be between 0 and 2^53.
- For the radix argument, the BASE function does not take any number below 2 or above 36. In other words, putting a numeric value of 1 and less or 37 and more will not work.
- For the min_length argument, the Excel BASE function allows you to use any number between 0 and 255. If you input a minus value or anything above 255, those values will never get recognized by the BASE function.
Now, if any numeric value does not maintain these criteria for respective arguments, you will always get a #NUM! error.
As a mini demonstration, we did not follow the requirement of the radix argument and put 1 and 40 as bases in two different calculations that returned the same #NUM! error. See the pictures below. The first one has 1 in its base argument.
And the second image here has 40 in its base argument. In both cases, we got the #NUM! error.
Then we changed the radix value to 36.
And we got a result without any error!
Here, we have shown you one reason behind the #NUM! error using a radix value that did not follow its criteria. This #NUM! error comes when any of the arguments has a numeric value that exceeds its limits. Be mindful of it, and you will easily eliminate this error!
Reason 4: Excel BASE and The #NAME? Error
The #NAME? error is pretty similar to the #VALUE! error we discussed in Reason 2. The only difference is that in the #VALUE! error, the number argument had a non-numeric value. But in this #NAME? error, the radix argument has a non-numeric value.
See the picture below as an example. The radix has “two” as its value, causing the #NAME? error.
Change it to a numeric value, and the error will go away. For instance, we changed the value to 2.
And now, the correct result is calculated.
That’s it!
Reason 5: Fraction Values Turning into Integers
If you have fractions in your number but see no fractions in the result, there are no errors or problems in the calculation. Rather, it follows one of the characteristics of the BASE function and its calculative operation.
This function works the whole numbers only. If the number is decimal, the function will consider just the whole part in its calculation, ignoring the fractional part.
Here, you can see that the number has fractions in it. If you look at the formula in the formula bar, it fulfills all the criteria of the BASE function. Yet, there is no fraction in the result or output.
The same is visible if we try to do a hexadecimal conversion.
And if you remove the fractional part from the number, the result stays the same.
Therefore, it is evident that the BASE function disregards the fractional part of a decimal number and picks up only the whole part of it. So if you do not get any fractional part in the result despite the number being a decimal one, it is all because of how the Excel BASE function works.
Last Words
We have reached the concluding section of this guide. We covered every single thing about the BASE function in Excel. In addition to demonstrating some uses, we also provided solutions to all types of errors and problems you may face while using this excellent function.
You should be able to use the BASE function without a problem after reading through the guide properly and following the demonstrations. If you cannot find any BASE function in your Excel, it is because of your Excel version. Any edition before Excel 2013 does not support this significant function.
Make sure to practice all the steps and error source detection processes a few times to grasp the concept and applications of this BASE function. It will not take more than 30 minutes to learn everything about this function and add it to your repertoire. Make the best use of this opportunity and prosper!
Similar Post: