Rob asks for tips on entering Nested IF functions in Excel. Today's episode shows how to use the Function Arguments dialog box to assist. Check out this link to a video that shows how to use the Name box for the second IF:
Transcript of the video:
MrExcel podcast is sponsored by Easy XL.
Learn Excel from MrExcel podcast episode 1831: Nested Functions Using the FX Button.
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Rob.
Rob is asking for tips on how to build nested functions and you know I started to think about this used to be available in the insert function's argument are the function arguments dialog box.
But, I can't figure out exactly what happened.
They used to be waited that you could launch into a new function argument.
If you know how to do this let me know.
I think it used to be an old version of Excel they must have just taken out.
So, here's our rules of a 1 is less is between 1 and 18 and low we start with that lowest one so if it's an A1<=18 and we say low.
Alright, and then we have more rules.
So, in here the method I came up with this type the word if open parenthesis, close parenthesis and we're going to dismiss function arguments by clicking on FX and then in the formula click anywhere inside that IF function and bring it back and that now brings us to a second set of function arguments for the second def statement.
So A1<=34 and medium.
Otherwise, another IF () open, closed parenthesis.
Hide the FX dialog box.
Click back on that IF and then bring it back A1<=50 then high.
Otherwise, super high.
click OK.
And see, the nice thing about this is because I used the FX button all three times I don't have to worry about having the right number of closing parentheses or I did handle that for me.
So, that might be a little bit easier than building that whole formula from scratch.
Now, while Rob's question was about building nested functions.
I just have to point out here that there especially if you have a lot of these there is a better way to go using the true version of the VLOOKUP.
So, what I've done here is I built a lookup table with the beginning category I guess it's technically should be one.
So, anything with the value of 1 gets assigned low and then anything up to 19 will be assigned medium.
So, we can do =VLOOKUP(A1,$D$3:$E$6,2,True) that value into this table F4 comma 2 comma you can actually leave the true off I always put it there just to remind myself, though, and so you can solve that with a vlookup nice thing here is you can handle 781,453 categories without having to continually nest the functions might be a much, much easier way to go.
All right.
Well.
Hey, I want to thank Rob for sending that question and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1831: Nested Functions Using the FX Button.
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Rob.
Rob is asking for tips on how to build nested functions and you know I started to think about this used to be available in the insert function's argument are the function arguments dialog box.
But, I can't figure out exactly what happened.
They used to be waited that you could launch into a new function argument.
If you know how to do this let me know.
I think it used to be an old version of Excel they must have just taken out.
So, here's our rules of a 1 is less is between 1 and 18 and low we start with that lowest one so if it's an A1<=18 and we say low.
Alright, and then we have more rules.
So, in here the method I came up with this type the word if open parenthesis, close parenthesis and we're going to dismiss function arguments by clicking on FX and then in the formula click anywhere inside that IF function and bring it back and that now brings us to a second set of function arguments for the second def statement.
So A1<=34 and medium.
Otherwise, another IF () open, closed parenthesis.
Hide the FX dialog box.
Click back on that IF and then bring it back A1<=50 then high.
Otherwise, super high.
click OK.
And see, the nice thing about this is because I used the FX button all three times I don't have to worry about having the right number of closing parentheses or I did handle that for me.
So, that might be a little bit easier than building that whole formula from scratch.
Now, while Rob's question was about building nested functions.
I just have to point out here that there especially if you have a lot of these there is a better way to go using the true version of the VLOOKUP.
So, what I've done here is I built a lookup table with the beginning category I guess it's technically should be one.
So, anything with the value of 1 gets assigned low and then anything up to 19 will be assigned medium.
So, we can do =VLOOKUP(A1,$D$3:$E$6,2,True) that value into this table F4 comma 2 comma you can actually leave the true off I always put it there just to remind myself, though, and so you can solve that with a vlookup nice thing here is you can handle 781,453 categories without having to continually nest the functions might be a much, much easier way to go.
All right.
Well.
Hey, I want to thank Rob for sending that question and I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
