Osman Goni Ridwan
You have come to the right place if you are looking for the answer or some unique tips to create a complex formula in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.
Download Practice Workbook
You can download the practice workbook from here:
What Is a Complex Formula in Excel?
In the complex formula, there is the usage of more than one Excel function or mathematical operator. And, in a simple formula, one single function or operator is used. Proper usage of parentheses and planning is needed to create a complex formula else it may return wrong output.
The Order of Operations in Formula of Excel – PEMDAS
Excel also follows the mathematical order of operations which is known as the PEMDAS rule. The description of the PEMDAS rule is as follows:
P = Parentheses: First, you will do the calculation of the operations enclosed in the parentheses
E = Exponential: Then, calculate the power and inverse power calculations.
MD = Multiplication and Division: After that, complete the calculations related to the multiplications and divisions whatever comes at first.
AS = Addition and Subtraction: At last, you will do the addition and subtraction calculations in the formula.
Steps to Create a Complex Formula in Excel
In this section, I will show you the quick and easy steps to create a complex formula in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.
We will use a formula to extract five digits numbers from any part of a string in Excel. We’ll use the CONCAT, SEQUENCE, MID, IFERROR, and LEN functions for the first time in this section. Moreover, we have changed our dataset slightly for this method. First, I am showing you the complex formula, and then I will show you how I have made it. This is the complex formula to extract five digits from any part of a string.
=CONCAT(IFERROR(0+MID(B5,SEQUENCE(LEN(B5)),1),""))
Road-map to Create a Complex Formula in Excel
First, you must make a roadmap for getting the target output. Our plan is as follows:
- First, we will calculate the length of the string.
- Then, we will insert all digits of the string individually.
- After that, we will add 0 with the digits, so only the numerical digits will give output and others will return an error.
- After excluding the error cells, we have to combine the other cells so we will get the numerical digits of the string.
📌 Step 1: Insert the LEN Function
- First, calculate the length of the string using the LEN function. For this, insert the following formula in cell C5:
=LEN(B5)
- The output will be 11 for the string in cell B5.
📌 Step 2: Use the SEQUENCE Function to Create a Serial
- After that, create a list of serials from 1 to 11 using the SEQUENCE function. For this, insert the following formula in cell D5:
=SEQUENCE(C5)
- As a result, you will see that a list of numbers from 1 to 11 is created in cell range D5:D15.
Read More: How to Create a Formula in Excel (5 Ways)
Similar Readings
- How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)
- Create a Formula in Excel for Multiple Sheets (4 Methods)
- Create a Formula to Calculate Percentage in Excel
- How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)
📌 Step 3: Add MID Function to Insert Characters of String in Individual Cells
- Now, I will use the MID function to insert all the characters of String in cell B5 in individual cells. For this, insert the following formula in cell E5:
=MID(B5,D5#,1)
- As a result, you will get all the characters of the string in cell B5 individually in cell range of E5:E15.
Note:
The D5# means the cell range D5:D15. As all the cells of this range are the output of the formula of cell D5, the range is converted to D5#.
Read More: How to Apply Formula to Entire Column Without Dragging in Excel
📌 Step 4: Use IFERROR Function to Remove the Letter Characters
- Now, I will add 0 with the cells of range E5:E15.
- As there are some numbers and some letters, only numbers can be added with zero and return output but the letters will return an error.
- Using the IFERROR function, I will eliminate the errors with a blank cell. For this insert the following formula in cell F5:
=IFERROR(0+E5#,"")
- Consequently, you will get the only number digits in cell F5:F15 and the remaining cells are blank.
Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)
📌 Final Step: Use CONCAT Function to Combine Cell Values
- Finally, you have to use the CONCAT function to combine cells of range F5:F14, so you will get the target output which was the only numerical digits of the string in cell B5.
- For this, insert the following formula in cell G5:
=CONCAT(F5#)
This is how you can create a complex formula in Excel. The main thing is: you have to brainstorm first about your target, then apply them step by step, and finally merge them into a formula. Thus you can create a complex formula.
Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)
Conclusion
In this article, you have found how to create a complex formula in Excel. I hope you found this article helpful. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.
