Art Has Several Functions Including Choose the Best Answer
The tutorial explains the syntax and basic uses of the CHOOSE part and provides a few not-trivial examples showing how to use a CHOOSE formula in Excel.
Choose is one of those Excel functions that may not expect useful on their ain, only combined with other functions requite a number of awesome benefits. At the almost basic level, you lot use the CHOOSE function to get a value from a listing by specifying the position of that value. Further on in this tutorial, you volition detect several avant-garde uses that are certainly worth exploring.
Excel CHOOSE function - syntax and basic uses
The CHOOSE function in Excel is designed to return a value from the listing based on a specified position.
The function is available in Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.
The syntax of the Cull office is as follows:
CHOOSE(index_num, value1, [value2], …)
Where:
Index_num (required) - the position of the value to render. It can be any number between 1 and 254, a jail cell reference, or another formula.
Value1, value2, … - a list of up to 254 values from which to choose. Value1 is required, other values are optional. These can be numbers, text values, cell references, formulas, or defined names.
Hither's an example of a CHOOSE formula in the simplest form:
=Choose(3, "Mike", "Sally", "Amy", "Neal")
The formula returns "Amy" because index_num is 3 and "Amy" is the 3rd value in the list:
Excel Cull part - 3 things to remember!
Choose is a very plain function and you will inappreciably encounter any difficulties implementing information technology in your worksheets. If the result returned by your CHOOSE formula is unexpected or non the result yous were looking for, information technology may exist because of the following reasons:
- The number of values to choose from is limited to 254.
- If index_num is less than ane or greater than the number of values in the listing, the #VALUE! fault is returned.
- If the index_num argument is a fraction, information technology is truncated to the everyman integer.
How to use Cull office in Excel - formula examples
The following examples show how CHOOSE can extend the capabilities of other Excel functions and provide culling solutions to some common tasks, even to those that are considered unfeasible by many.
Excel Cull instead of nested IFs
One of the most frequent tasks in Excel is to render different values based on a specified status. In most cases, this can be washed by using a classic nested IF statement. But the Cull part tin exist a quick and easy-to-sympathize alternative.
Instance i. Render different values based on status
Supposing you have a column of student scores and you want to label the scores based on the post-obit conditions:
| Result | Score |
| Poor | 0 - l |
| Satisfactory | 51 - 100 |
| Good | 101 - 150 |
| Excellent | over 151 |
I way to do this is to nest a few IF formulas inside each other:
=IF(B2>=151, "Splendid", IF(B2>=101, "Expert", IF(B2>=51, "Satisfactory", "Poor")))
Some other way is to cull a label respective to the condition:
=Choose((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), "Poor", "Satisfactory", "Good", "Excellent")
How this formula works:
In the index_num argument, you evaluate each condition and return TRUE if the condition is met, FALSE otherwise. For example, the value in cell B2 meets the first three conditions, so we get this intermediate result:
=Cull(True + Truthful + TRUE + False, "Poor", "Satisfactory", "Good", "Excellent")
Given that in virtually Excel formulas TRUE equates to one and FALSE to 0, our formula undergoes this transformation:
=Choose(1 + 1 + one + 0, "Poor", "Satisfactory", "Adept", "Excellent")
After the addition functioning is performed, we have:
=Cull(3, "Poor", "Satisfactory", "Proficient", "Excellent")
As the result, the iiird value in the list is returned, which is "Good".
Tips:
- To brand the formula more flexible, y'all can employ jail cell references instead of hardcoded labels, for instance:
=CHOOSE((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), $E$1, $East$two, $E$three, $E$four) - If none of your weather condition is TRUE, the index_num argument will be set to 0 forcing your formula to render the #VALUE! error. To avoid this, simply wrap CHOOSE in the IFERROR function like this:
=IFERROR(CHOOSE((B2>0) + (B2>=51) + (B2>=101) + (B2>=151), "Poor", "Satisfactory", "Proficient", "Splendid"), "")
Example 2. Perform different calculations based on condition
In a similar way, y'all tin can use the Excel Choose function to perform one adding in a serial of possible calculations/formulas without nesting multiple IF statements within each other.
Every bit an example, allow'south calculate the commission of each seller depending on their sales:
| Commission | Sales |
| v% | $0 to $fifty |
| seven% | $51 to $100 |
| x% | over $101 |
With the sales amount in B2, the formula takes the following shape:
=CHOOSE((B2>0) + (B2>=51) + (B2>=101), B2*5%, B2*7%, B2*10%)
Instead of hardcoding the percentages in the formula, you can refer to the corresponding cell in your reference table, if there is any. But remember to prepare the references using the $ sign.
=Choose((B2>0) + (B2>=51) + (B2>=101), B2*$E$2, B2*$E$3, B2*$E$4)
Excel Cull formula to generate random data
As you probably know, Microsoft Excel has a special role to generate random integers between the bottom and top numbers that you specify - RANDBETWEEN function. Nest it in the index_num argument of CHOOSE, and your formula will generate almost whatsoever random data yous want.
For example, this formula can produce a list of random exam results:
=Choose(RANDBETWEEN(ane,four), "Poor", "Satisfactory", "Good", "First-class")
The formula's logic is obvious: RANDBETWEEN generates random numbers from 1 to 4 and CHOOSE returns a corresponding value from the predefined list of iv values.
Note. RANDBETWEEN is a volatile function and it recalculates with every alter yous make to the worksheet. As the upshot, your list of random values will as well change. To forbid this from happening, you lot can supplant formulas with their values by using the Paste Special characteristic.
CHOOSE formula to do a left Vlookup
If you take ever performed a vertical lookup in Excel, you know that the VLOOKUP function can only search in the left-almost cavalcade. In situations when you lot need to render a value to the left of the lookup column, you can either use the INDEX / Match combination or trick VLOOKUP by nesting the CHOOSE office into information technology. Here'south how:
Supposing you have a listing of scores in column A, educatee names in column B, and you want to retrieve a score of a particular student. Since the return column is to the left of the lookup cavalcade, a regular Vlookup formula returns the #N/A error:
To set this, become the Cull office to swap the positions of columns, telling Excel that column 1 is B and cavalcade 2 is A:
=Cull({1,ii}, B2:B5, A2:A5)
Because we supply an array of {1,2} in the index_num argument, the CHOOSE function accepts ranges in the value arguments (normally, it doesn't).
Now, embed the to a higher place formula into the table_array statement of VLOOKUP:
=VLOOKUP(E1,Choose({ane,2}, B2:B5, A2:A5),2,FALSE)
And voilà - a lookup to the left is performed without a hitch!
Cull formula to return next working day
If you are not certain whether you lot should become to work tomorrow or can stay at home and bask your well-deserved weekend, the Excel Cull function can find out when the next work day is.
Assuming your working days are Monday to Friday, the formula goes every bit follows:
=TODAY()+Choose(WEEKDAY(TODAY()),1,i,i,i,1,iii,two)
Tricky at start sight, upon a closer look the formula's logic is like shooting fish in a barrel to follow:
WEEKDAY(TODAY()) returns a serial number corresponding to today's date, ranging from ane (Lord's day) to 7 (Saturday). This number goes to the index_num statement of our Cull formula.
Value1 - value7 (one,1,1,ane,one,3,two) determine how many days to add to the current date. If today is Sunday - Th (index_num 1 - v), you add one to return the next day. If today is Friday (index_num 6), yous add together three to return next Monday. If today is Saturday (index_num 7), you add two to return next Monday again. Yep, information technology'southward that simple :)
Choose formula to return a custom day/month name from date
In situations when yous want to get a day name in the standard format such as total proper name (Monday, Tuesday, etc.) or brusk proper name (Mon, Tue, etc.), you tin use the TEXT office as explained in this example: Get day of week from date in Excel.
If you wish to return a 24-hour interval of the week or a month name in a custom format, use the CHOOSE function in the following manner.
To get a day of the week:
=Cull(WEEKDAY(A2),"Su","Mo","Tu","We","Thursday","Fr","Sa")
To get a month:
=Choose(MONTH(A2), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","December")
Where A2 is the prison cell containing the original date.
I hope this tutorial has given you some ideas of how yous can use the Choose function in Excel to enhance your information models. I thanks for reading and hope to meet you lot on our blog next week!
Download practice workbook
Excel CHOOSE office examples
You may also exist interested in
Source: https://www.ablebits.com/office-addins-blog/2018/05/30/choose-function-excel-formula-examples/
0 Response to "Art Has Several Functions Including Choose the Best Answer"
Post a Comment