What choose() does

Last week I quickly went over the syntax of the choose() function, and how to get to a help wizard to help you build one, but I really didn’t discuss what it does and what it’s good for.

Basically, it’s a way to condense a really complex if() statement into a much more simple format.

For example, these two formulas would have (almost)* the same result:
=if(a1=1,”one”, if(a1=2,”two”, if(a1=3,”three”, if(a1=4,”four”, if(a1=5,”five”, if(a1=6,”six”,”#ERR”))))))
=choose(a1,”one”,”two”,”three”,”four”,”five”,”six”)

*I said (almost) because the if() will say “#ERR” if a1 isn’t between 1 and 6, but the choose will actually produce an error.

Another possible example is the one I built for HTML style coding:
=choose(bin2dec(style)+1,style1,style2,header,header,total1,total2,total_header,total_header,blank)

The first part, bin2dec(style)+1, uses bin2dec to convert “style” from a binary (base 2) number to a normal decimal (base 10) number, which is what we’re used to using. It then adds one because choose() always starts with one. So the various options for style() become:

blank corners last/total column/row first/header column/row odd row Original bin2dec() +1
0 0 0 0 0 0 1
0 0 0 1 1 1 2
0 0 1 0 10 2 3
0 0 1 1 11 3 4
0 1 0 0 100 4 5
0 1 0 1 101 5 6
0 1 1 0 110 6 7
0 1 1 1 111 7 8
1 0 0 0 1000 8 9

1 = even row, not first, not last, not corner: style1
2 = odd row, not first, not last, not corner: style2
3 = even row, first/header, not last, not corner: header
4 = odd row, first/header, not last, not corner: header
5 = even row, not first, last/total, not corner: total1
6 = odd row, not first, last/total, not corner: total2
7 = even row, first/header, last/total, not corner: total_header
8 = odd row, first/header, last/total, not corner: total_header
9 = n/a, n/a, n/as, corner: blank

So these nine styles are the 9 options listed in the second part of the choose() function. Some of them (header, total header) are duplicated because both even and odd rows have the same format, but trying to code that into the if() statement that generated the numbers would have been excessively complicated.

Next week I’ll show you another example, using mod() to create an auto-filling timesheet.