How to Create a Complex Formula in Excel (with Easy Steps)

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. There are some easy steps 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),""))

Creating a Complex Formula in Excel


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.

Using LEN Function to Create a Complex Formula in Excel


πŸ“Œ 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.

Using Sequence function to Create a Complex Formula in Excel

Read More: How to Create a Formula in Excel (5 Ways)


Similar Readings


πŸ“Œ 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#.

Using MID function in Excel

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.

Using IFERROR function to Create a Complex Formula in Excel

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#)

Using CONCAT function in Excel

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.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo