{"id":24491,"date":"2021-07-02T00:10:57","date_gmt":"2021-07-01T22:10:57","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24491"},"modified":"2024-02-06T16:06:04","modified_gmt":"2024-02-06T15:06:04","slug":"understand-complex-formulas-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/understand-complex-formulas-in-excel\/","title":{"rendered":"Having Trouble Understanding Complex Formulas in Excel?"},"content":{"rendered":"

\"Break<\/a><\/p>\n

Excel has bombarded its users with a plethora of functions and formulas<\/a><\/strong> that can help solve any person\u2019s problems and speed things up.<\/p>\n

Having too many formulas gives rise to the problem of understanding the inner workings<\/strong> of formulas. It can be challenging to understand how complex formulas in Excel <\/strong>works<\/strong>, what to enter in each argument, and what is the step-by-step process of the formula<\/a>.<\/p>\n

In this article, we will cover how to write a complex formula using an Argument Wizard <\/a><\/strong>and how to understand the workings of a formula using Evaluate Formula<\/a>.<\/strong><\/p>\n

So, without further ado. Let us dive in! Download this Excel workbook<\/strong> so you can practice along with us:<\/p>\n

\n

download excel workbook<\/strong><\/span>Understand-Excel-Formula.xlsx<\/span><\/a><\/p>\n<\/div>\n

 <\/p>\n

<\/a>Arguments wizard<\/strong><\/span><\/p>\n

Sometimes, you may not be sure how a specific formula works or what will be the syntax of that formula. The best way of understanding these requirements<\/strong> is through the formula arguments wizard.<\/p>\n

To better understand this, let us take the example using the VLOOKUP formula. Here, we need to extract the salary of Rachael in cell D15<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

Let’s open the arguments wizard for the VLOOKUP formula.<\/p>\n

 <\/p>\n

STEP 1: <\/strong><\/span>Enter the VLOOKUP function: =VLOOKUP(<\/strong><\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 2: <\/strong><\/span>Press Ctrl+A<\/strong> or click on the fx button<\/strong> in the formula bar to open the arguments wizard<\/strong> window.<\/p>\n

\"Having<\/a><\/p>\n

This is how the formula wizard looks like.<\/p>\n

It shows all the arguments<\/strong> in sequential order. It then explains the use of the function <\/strong>and\u00a0the details of the argument<\/strong> selected.<\/p>\n

\"Having<\/a><\/p>\n

STEP 3: <\/strong><\/span>Select the first argument i.e. Lookup_value<\/strong>. Here, cell C15 contains the name, Rachael.<\/p>\n

\"Having<\/a><\/p>\n

You can also see the description of the first argument<\/strong> is written at the bottom of the dialog box.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 4: <\/strong><\/span>Select the second argument i.e. Table_array<\/strong>\u00a0and select the table range<\/strong> from where you can search i.e. A7:C13<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 5: <\/strong><\/span>Select the third argument<\/strong> i.e. col_index_num<\/strong> and enter the column number from which to extract the data. In this example, it should be 3.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 6: <\/span><\/strong>Lastly, select the fourth argument<\/strong> i.e. range_lookup<\/strong>. Since we want an exact match type FALSE or 0<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

After entering the details, the wizard would look like below. It would even show a preview of the argument values and the answer below<\/strong> it if the inputs were correct.<\/p>\n

\"Having<\/a><\/p>\n

STEP 7: <\/strong><\/span>Press OK<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

The formula will be entered in cell D15<\/strong> and it will show the result, i.e., the salary of Rachael – $16,540<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

<\/a>Evaluate Formula<\/strong><\/span><\/p>\n

We can even use evaluate formula feature in Excel to understand how complex formulas in Excel works step by step<\/strong> and provides the final result.<\/p>\n

It solves the problem that we face countless times wherein we have a hard time understanding formulas. It is useful in mastering how a formula works<\/strong> and it is also a great tool for debugging<\/strong>.<\/p>\n

Let’s example look at the formula below. Here, we want to check if the average salary is greater than or equal to the standard<\/strong> set i.e. $12,000.<\/p>\n

\"Having<\/a><\/p>\n

Now, let us use the evaluate formula feature to see the workings of complex formulas in Excel!<\/p>\n

 <\/p>\n

STEP 1:<\/strong><\/span> Select the cell (i.e. C15) that contains the formula<\/strong> you want to evaluate.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 2:<\/strong><\/span> Go to Formulas<\/strong> > Evaluate Formula<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 3:<\/strong><\/span> The formula will now be displayed in the Evaluate Formula dialog box<\/strong> and the part of the Excel complex formula that will be evaluated first is underlined<\/strong>.<\/p>\n

Click Evaluate<\/strong>.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span> The average will be calculated and displayed as 13662.8514….<\/p>\n

Click Evaluate.<\/p>\n

\"Having<\/a><\/p>\n

STEP 5:<\/strong><\/span> Since the average is greater than 12000<\/strong> and the logical condition of the IF formula is met, it will display TRUE<\/strong>.<\/p>\n

Click Evaluate.<\/p>\n

\"Having<\/a><\/p>\n

STEP 6:<\/strong><\/span> Lastly, the text if the condition is met is Yes. So yes is displayed.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

You can click either Restart<\/strong> to see the step-by-step evaluation again or Close<\/strong> to close the dialog box.<\/p>\n

\"Having<\/a><\/p>\n

 <\/p>\n

Conclusion<\/span><\/h3>\n

Now you can easily open the function arguments wizard to understand the requirements of the complex formulas in Excel<\/strong>\u00a0and each of its arguments. This will help you work with newer functions and improve your operations<\/strong> with functions you are already familiar with.<\/p>\n

Also, you can now use evaluate formula to see how a formula gave us the result in a few easy steps<\/strong>!<\/p>\n

We hope this tutorial helped you to understand and learn Excel formulas better and aid you in smooth functioning on Excel.<\/p>\n

 <\/p>\n

Make sure to download our FREE PDF on the\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

Excel has bombarded its users with a plethora of functions and formulas that can help solve any person\u2019s problems and speed things up. Having too many formulas gives rise to the problem of understanding the inner workings of formulas. It can be challenging to understand how complex formulas in Excel works, what to enter in […]<\/p>\n","protected":false},"author":1,"featured_media":24588,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[10],"tags":[757,755,756],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24491"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=24491"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24491\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/24588"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=24491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=24491"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=24491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}