Wednesday 7 November 2018

Microsoft Excel CHOOSE-Function

CHOOSE Function 


Where you are processing numeric values it is often easier to use an indexing function than a logical one. For example, cell reference A1 contains the values of either 1, 2 or 3 and another formula needs to react to these values by returning either "Tom", "Dick" or "Harry". The CHOOSE function returns the nth item from an internal list where n is the value of the first argument:

=CHOOSE(A1,"Tom","Dick","Harry")

The equivalent formula using IF would be far more involved:

=IF(A1=1,"Tom",IF(A1=2,"Dick","Harry"))

The indexed elements in CHOOSE can be any value, a cell reference or a calculation. In the next example we need to return a value to a cell based on which day of the week it is, on Monday we must have a 20, on Tuesday, 34 etc. The NOW function returns the current date value, the WEEKDAY function returns a value from 1 to 7 and the CHOOSE function picks the correct value from the list:

=CHOOSE(WEEKDAY(NOW(),2),20,34,89,12,105,232,232)

NB* The second argument for the WEEKDAY function specifies the value order for the days of the week, argument value 2 means that Monday is 1 and Sunday is 7.
Often, the indexed listing inside the function avoids your having to work out calculations logically.

For example; an organisation uses a financial year which starts in April and we have to express the month number as a value from 1 to 12, April is 1, May is 2 etc. A combination of the functions

MONTH and NOW will give the calendar month as a number but in the usual order; January is 1, February is 2 etc. You could work out logically how to transform the calendar month number into the required month number but it so much easier with CHOOSE:

=CHOOSE(MONTH(NOW()),10,11,12,1,2,3,4,5,6,7,8,9)



For Further Course Details Visit Our Homepage












Microsoft Accredited IT Training Provider
Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators
Microsoft MOS Test Centre London

No comments:

Post a Comment