Advanced Decision Making using CHOOSE in
HTML-код
- Опубликовано: 22 авг 2024
- Excel File: www.teachexcel...
Excel Forum: www.teachexcel...
How to make decisions in Excel without using an IF statement; here, we use the CHOOSE() function to make dynamic and advanced logical tests that eliminate the need for nested IF functions, in many cases.
The CHOOSE() function is a really helpful and powerful function that allows you to return a value or perform an action based on an index number that you supply the function. The cool thing is that you can return range references as well as text or number values - this allows you to make complex dynamic formulas.
This feature is also one of the building blocks for large complex Excel models and scenario analysis techniques because it allows you to build a worksheet that can completely change with the adjustment of a single cell.
I hope you find this tutorial helpful!)
TeachExcel.com
Fantastic! Was barely familiar with the CHOOSE function until I saw this video. You've motivated me to replace nested IFs with CHOOSE as well as to explore other uses of this versatile function in my daily work. Thanks a ton!
The choose function is a great way to get rid of the volatile function "Indirekt" that many people use to dynamically reference to different sheets which is not possible with Index-Match or Vlookup
A great example of this is used to determine various holidays that fall always on specific days, i.e. Mother's day is always the 2nd Sunday in May. You can use: =DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1) where the year is in cell A3 on a spreadsheet.
The formula breaks out to =(Date(May 1st, [year entered in A3]) + (7days) + (the number returned from determining the Weekday of May 1st as the index, and the value [0,6,5,4,3,2,1]).
i.e. in 2020, the Second Sunday is May 10th.
May first, plus 7 days is always May 8th.
May first falls on a Friday, so the WEEKDAY portion returns a 6. This is how the Index Number is determined. We want to return the 6th value from the list...
The Choose Function has a value list of 0,6,5,4,3,2,1 -- the 6th value is 2.
Thus: May 1st is a Friday, Plus 7 Days is Friday May 8th, Plus 2 more days gets you to May 10th, the second Sunday of the month for 2020.
To Test this, set up a sheet, enter the year only in Cell A3, then copy/paste this formula into another cell.
The real heroes are in the comments
Damn where you been my whole life, just found this channel. Great stuff!
Thanks for the video. To make it very clear, it wlould be good to show also what t would have been wth IF fuction
Great video. Now I can imagine its many practical uses in my work!! Thank you!!
Just subbed. Great tutorial. Clear and concise. Thank you.
I've seen a couple of vids but this one made me sub. Great job!
Thanks! Haven't used it yet. I'll probably stick to the trusted Index/Match combo with a named range or a table reference, though.
The last 'complicated' example is more easily done and way more flexible using a pivot table.
Superb dear. Awesome method of explanation. 👍 From India
Exccelent tip! I have never used this one, it's a lot helpful!!!
For most of these examples, it would be better to use the SWITCH() function. But but very interesting anyway as I didn’t know that CHOOSE() existed.
Very well explained! Thank you so much.
I like your video but still dont get the use of choose function that you couldnt solve kind of easier and with less harded coded stuff with vlookup
so it is array indexing? for an array of strings?
Thank for the video..👌👌👍🏻❤ its great😊
Good stuff, thanks!
Great tutorial! Easy to understand.
Can this work with Index(array,Match()) to let you know if you have multiple values that are the same, or like a VLookup if you want to look up the 2'nd or 3'rd occasion the look up value occurs? I recently told someone in an interview that the most complicated formula I've ever written was a nested if Formula. Obviously he thought I had no idea what I was talking about but you do! You mentioned something about it being able to help in this way in the beginning of the video. I'm an accountant and have to reconcile a lot of data and let's say you are doing payroll for thousands of employees so you are bound to have employees who get paid the same amount. I use the index match (with a double match function instead of just one [match (1,(A1:A500=D1)*(B1:B500=E1)]. It turns the formula into an array and then I have to do it for the other set of data to create references. I then go and find duplicates because it only gives you the first match and manually change those and finish it off by using one reference as the master file and creating a VLookup to a whole new generated set of numbers to create the true tick marks..... If I can get this to count the number of times an occurrence shows up in the data and put a .1 or .2 after the original reference number from the index match I wouldn't need to do the manual part or anything after that. I hope you were able to follow along. I'm a nerd, if you read this thanks for your time!!!!!
Nicely explained tutorial sir
Spring stars in February!
value := array[1]
If you use Choose with True/False, isn't it easier to simply use If?
Good insight However I would have preferred to have the examples in different sheets, as the one you presented is too crowdedd and distracting.
Hi, i have dought on hyperlink, i will use concetate fonction using in herperlink, colums have same file name, i will use above function to view the listing link in another colums , it's working, but i cat link sub folders, give any idea,
=Hyperlink(contecate("//file path/",file name or cell value,"file type"),"friendly name"))this formula i have using, pls give any suggestions for sub folders
Hi! Can u explain choos function with vlookup criteria
I think you would use Vlookup to determine the potential Index Number which would point you to the proper value in the value set you determine in the secondary parts of your arguments.
Google the reverse look up which will use VLOOKUP concept
Are you Ryan Reynolds?
Xx
Choose with a logic function seems no different than an IF statement.