Excel Use The Function Wizard Wisely


Nested functions in excel
A new best friend…
During one of my courses i stumbled up on the following situation. One of my students was using nested functions and used the function wizard to accomplish his task. What caught my attention was the he was copying and pasting his nested function. In other words he was nesting functions by using the function wizard, closing the wizard, copying and pasting the rest of the functions in the formula bar. I asked him if he knew that he could accomplish the same thing using the function wizard. He didn’t and told me that this was the technique he used all of his Excel career. I showed him how he could do it and he and i are best friends for ever now 😉

Let me show you how you can nest functions in Excel without leaving the function wizard.
In this post i will show you two examples of nested functions in Excel using the function wizard. The first one is a simple example using the same function twice. The second example is more complex showing three different functions in one formula just to show the technique. Remember the example is simple and is just to show the technique!

A simple IF function used twice
We start of with a simple IF function in Excel using the function wizard. So in cel A1 i entered 100 and placed the active cel to cel C2 (for readability). I click the Function button next to the formulabar and the Insert function dialog appears.

















With the IF function selected i click OK and the function wizard appears. So in the first argumentbox i typ my logical test which in this case is “Check to see if the value of cel A1 is greater than 100”. This translates into =A1>100. Now the great thing about the function wizard is that you don’t have to think about the = sign because that will be placed there for you. Furthermore you don’t have to think about the argument separators as well because when you click on the next argument box Excel will place the argument separator as well. In my case the separator is a semicolon but that’s because i have regional settings from the Netherlands. UK layout will need a comma as a separator. The final great thing about the wizard is that for text arguments the wizard will place double quotes around the text so you don’t have to bother about that either. And i almost forgot that most important issue the final brackets around the formulas and functions. How many times did you forget a closing bracket around your formula? Well using the wizard, you guessed it, Excel will place them for you!
















Let’s move on. I entered the logical test and added a description in the True argument box. So if the value in A1>100 it will show “In Stock”. Now i have two remaining options and only one argument box. So to solve this i start a new IF function. Don’t close the function wizard but place the cursor in the  “Value_if_false” argument box. With that in place now click the Functions name box again which in this case still states “IF“. That’s exactly what we need. By clicking on the IF the function wizard shows a new dialog for the IF statement as show below. Watch the formula bar. This has not changed but shows you that you have nested an IF function in a IF function. Fill in the dialog again and click OK.
















What is most important here is that you can click OK and that Excel has taken care of everything for you. Did you see that you accomplished a nested function without leaving the function wizard? Great stuff he?

A more complex example

I know what you are thinking and i will show you another example which is a little more complicated. The next sample shows how you can nest three different functions with the help of the function wizard. Want to try? Follow these steps.

Here is the question i want the answer to: IF the value in cel A2 eauals 100 AND cel B2 eaquals 1 then LOOKUP the value from B2 in the list and show me the answer from the second column from the list. As you can see i need to use three functions to get my answer. Here we go.

We start of with typing in the various values needed in the cells. I placed the active cell to D2 where i will place my formula. I click the function button next to the formula bar and start of with an IF function. Because i have to evaluate two cells i need an AND function. Now here’s where most people go doing their tricks but you don’t have to. Look at the picture below and you will see that with the cursor in the first argument box from the IF function i click the function name box and search for the AND function. In my case Excel has remembered that i’ve used this one before so it’s in the list. If it is not just click the option “more functions” and click the one that you need. In this case we need the AND so i click on that one from the list. The function wizard changes to the AND function wizard settings. Don’t be scared nothing serious happend yet. Take a look at your formulabar and you will see that Excel has added the AND function into the IF function.
















I fill in the necessary conditions in the various argument boxes (A2=100 – B2=1). I do not typ any semicolons because Excel will do that for me. Now here comes the trick. With the AND arguments neatly filled click on the IF word in the formulabar. You will see that the function wizard returns to the original IF function settings and the logical argument box is now filled with your first AND statement. Now we need to fill the second argument of the IF function which is the True part (so conditions are met).

















I click on the “Value_if_true” argument box and my next step is to click on the function namebox again searching for the VLOOKUP function. If you have found it click on it and the function wizard will changes his settings that apply to the lookup function. Fill in the necessary arguments for the VLOOKUP functions but don’t click OK. If you have filled the last argument take a look at the formula bar again. You will notice that Excel has provided you with a nifty nested function without leaving the function wizard. Now to top things of you have to finish the final argument for the IF function which is the “Value_if_false” argument box. So we need to get back to our wizard settings for the IF function. With the function wizard still open click on the IF word in the formulabar. This will show the function wizard settings for the IF function again leaving you only to fill the final argument box. I typed “standard edition” here for the example but by now you know that you can start a new function again here creating a mega formula!

If you look at the first picture in this blogpost you will see the final formula containing three nested functions all created without leaving the function wizard. So in the future no more cutting and pasting but use the function wizard to the full extend.

Happy wizarding 🙂

Advertisements

7 thoughts on “Excel Use The Function Wizard Wisely

  1. Stuart

    Hi Maurice,

    Thanks for your article, what I was trying to find out was “What is Microsoft’s Official name for the Name Box when you are Entering formulas.” You called it “Functions name box” that sounds good to me.

    I also think of Insert Function as a Wizard, as indeed that was what it was called. Sadly, I think of him as my friend. But I have to inform you that he has been renamed (Twice), first he became “Paste Function” way back and more recently “Insert Function”.

    Regards.
    Stuart

    Reply
    1. Maurice Post author

      Hi Stuart,

      I always use the names presented by the tooltip. So you are correct on your last comment. When the tooltip appears it states “Insert Function”. So my bet would be that this would be the official name.

      Maurice

      Reply
  2. Tuamoitou

    “And i almost forgot that most important issue the final brackets around the formulas and functions. How many times did you forget a closing bracket around your formula? Well using the wizard, you guessed it, Excel will place them for you!”

    If you type =function(….. and push Enter Excel will put for you the last closing bracket.

    Reply
    1. Maurice Post author

      Indeed, but that only works if you have a single function. When you are nesting functions Excel will not place all the missing brackets 🙂 That’s where the wizard might come in handy!

      Reply
  3. Charles Aronson

    I am trying without success to use the function wizard to calculate the intrinsic value of a 2 legged stock option position, a short call and a long put. It has 3 arguments:
    1. If the current price is equal to or greater than the put strike price, but less than the call strike price, display the net premium collected on the position, which is the premium from the short put minus the cost of the long call.

    2. If the current price is less than the put strike, calculate and display (current price – put strike)*number of contracts*100) + net premium

    3. If the current price is greater than the call strike, calculate and display (current price – call strike)*number of contracts*100) + net premium.

    Reply
  4. Maurice Post author

    Hi, My guess would be that you need to start with an IF constuction. In that IF construction use the AND function to combine the first two criteria and after completing thet return to the original IF statement. From there start a new IF statement in the ‘False’ part and put in the remaining two arguments. That should do the trick.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s