Too Many Options



In this post, I thought it would be interesting to show just how flexible spreadsheets can be.


For a client's project, I had to create a schedule where the number of days shown would change based on the name of the month in the worksheet.


Image by Erika Wittlieb from Pixabay


Cell A1 had the name of the month.


Cell B1 had this formula - =MONTH(A1), which returned a digit equal to the month number. So January was 1, February was 2, and so on.


I decided the best approach was to have a formula that would choose months that had 30 days. If the month had 30 days, the formula would return 1 (TRUE). Otherwise, the formula would return 0 (FALSE).


This was the formula I came up with:


=IF(OR(B1=4,B1=6,B1=9,B1=11),1,0)


I thought, however, that maybe there was a better way to write this formula. So I posted to a developers' group, asking for suggested alternatives. Quite a discussion ensued!


More interesting was the different formulas everyone came up with - here are the 9 alternatives that were offered:


  • = OR ( B1 = { 4, 6, 9, 11 } )

  • = DAY ( EOMONTH ( B1 ), 0 ) < 31

  • = HLOOKUP ( B1, { 4, 6, 9, 11 }, TRUE, 0 )

  • = CHOOSE (B1, 0, 0, 0, 4, 0, 6, 0, 0, 9, 0, 11, 0 )

  • = CHOOSE ( MONTH ( A1 ), , , , 1 , , 1 , , , 1 , , 1 , )

  • = MID ( "303232332323", B1, 1 ) < "3" )

  • = IFERROR ( DATEVALUE ( "31" & A1 ), A1 )

  • = MONTH ( B1 ) <> MONTH ( DATE ( , MONTH (B1), 31 ) )

  • = ISODD ( MONTH ( B1) + ( MONTH ( B1 ) > 7 ) )


Which is the best alternative? Well in my case, the first one on the list was a better solution to what I created because it was just easier to understand.


However, depending on what the spreadsheet was designed to do, one of the other formulas may have been a better answer.


Just goes to show you how incredibly flexible spreadsheets can be. No wonder they make so many people crazy!





0 views