Microsoft Office Tutorials and References
In Depth Information
Based on a list of arguments, CHOOSE returns the index number from the list.
The CHOOSE function can be used as a standalone function or in conjunction with other
functions. The CHOOSE function can also be automated with form controls where the index number is
the cell link and value 1, value 2 are automatic results from the form controls cell link. Because
of the number of examples in Figure 9.1, the examples are numbered down the left side. The
CHOOSE premise is quite simple, the index number indexes the value within the formula, the
value can be text, cells, and ranges. The index number can also be a cell reference.
INDEX_NUM Specifies which value argument is selected and must be a
number between 1 and 29.
VALUE 1, VALUE 2,... The values are arguments from 1 to 29. These numbers can be
cell references, formulas, functions text, and defined names.
Consider the following examples using the CHOOSE function:
1. =CHOOSE(1, 1 ,2,3,4,5,6,7,8,9) results in 1 because the index number calls for the
first choice in the value list (1–9).
2. =CHOOSE(9,1,2,3,4,5,6,7,8, 9 ) results in 9 because the index number calls out for 9
index spaces to the right.
3. =SUM(CHOOSE(1, D20:D33 ,E20:E33)) results in the sum of D20:D33 because the index
number calls out for the sum of the first range over.
4. =COUNT(CHOOSE(1, D20:D36 ,E20:E36)) results in the count of record in the range
D20:D36 because the index number calls out for the count of the first range over.
5. =SUM( E$20 :CHOOSE(1, E$22 ,E$26,E$30)) results in the sum of the range E20:E22.
6. =SUM( E$20 :CHOOSE(2,E$22, E$26 ,E$30)) results in the sum of the range E20:E26.
7. =SUM( E$2 0:CHOOSE(3,E$22,E$26, E$30 )) results in the sum of the range E20:E30.
A more powerful way to use the CHOOSE function is to automate its index number with a cell
reference. For example, use the CHOOSE function with the Option button from the Form Control
toolbar. Notice that in the example in Figure 9.2, the CHOOSE formula references the cell link cell
D7, which prompts an automated response for the indexing result. In this example, Approve
equals 1 and would produce the text result of APPROVE . If Decline equals 2, it would produce a
text result of DECLINE . Where Send To Mgr. equals 3, the text result would be SEND TO MGR . To
learn more about formulas and form controls, see Chapter 2, “Managing Your Business with
Functions.” A simple nested IF statement returns a result based on the CHOOSE result; and a final
IF statement returns a result based on the previous nested IF result. This simple example of
using one formula response generated from another formula’s result demonstrates how forms
are created using form controls.