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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Create a Complex Formula in Excel: Step-by-steps

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-digit 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 for Multiple Cells 


📌 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


📌 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


📌 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 Insert Formula for Entire Column in Excel


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

In this article, you have found how to create a complex formula in Excel. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo