Learn Excel - Nested Functions with fx - Podcast 1831

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Dec 2, 2013.
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:
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top