Excel's SWITCH function is similar to Excel's IF and IFS functions and is the topic of this post. First, a review of the IF and IFS functions.
Users of Microsoft Excel may utilize the IF function to apply a logical test and if true return a result and if false return a different result. The IF function can apply mathematical operators and perform additional calculations. You can also nest multiple IF functions together in order to perform multiple comparisons.
The post Using Excel's IFS Function showed a case of using nested IF functions to determine the number of decimal places required to properly format price data.
The nested IF function:
=IF(N2=0,"#",IF(N2=1,"#.0",IF(N2=2,"#.00",IF(N2=3,"#.000",IF(N2=4,"#.0000",IF(N2=5,"#.00000",IF(N2=6,"#.000000",IF(N2=7,"#.0000000"))))))))
Cell N2 in the sample available from the post calculated the number of decimal places using the RTD formula for Tick size (TSize):
=IF(LEN(RTD("cqg.rtd", ,"ContractData", "Tsize("&A2&")", "LastQuoteToday",,"T"))=1,0,LEN(RTD("cqg.rtd", ,"ContractData", "Tsize("&A2&")", "LastQuoteToday",,"T"))-2)The goal was to allow the user to change the symbol in a cell, for example Cell A2, that was the symbol reference cell for other RTD formulas, such as Open, High, Low, and Close and not require the user to have to manually reformat the Open, High, Low, and Close cells to the proper number of decimals.
The IFS function was introduced as a method that avoids using nested IF functions and thus is a simpler format and easier to test:
=IFS(N2=0,"#",N2=1,"#.0",N2=2,"#.00",N2=3,"#.000",N2=4,"#.0000",N2=5,"#.00000",N2=6,"#.000000",N2=7,"#.0000000")
The IFS function is available in the 2019 and Office 365 versions of Excel.
A simpler approach is available: The SWITCH function (introduced in Excel 2016).
=SWITCH(N2,0,"#",1,"#.0",2,"#.00",3,"#.000",4,"#.0000",5,"#.00000",6,"#.000000",7,"#.0000000)
The SWITCH function only requires one expression (the formula above is in cell N2) and then checks for the results and stops at the first true in the formula and returns that result.
Comparing the three functions:
=IF(N2=0,"#",IF(N2=1,"#.0",IF(N2=2,"#.00",IF(N2=3,"#.000",IF(N2=4,"#.0000",IF(N2=5,"#.00000",IF(N2=6,"#.000000",IF(N2=7,"#.0000000"))))))))
=IFS(N2=0,"#",N2=1,"#.0",N2=2,"#.00",N2=3,"#.000",N2=4,"#.0000",N2=5,"#.00000",N2=6,"#.000000",N2=7,"#.0000000")
=SWITCH(N2,0,"#",1,"#.0",2,"#.00",3,"#.000",4,"#.0000",5,"#.00000",6,"#.000000",7,"#.0000000")
The number of characters is 139, 112, and 94 respectively. Clearly, the SWITCH function would be easier to troubleshoot.
However, a popular form of nested IF functions include using And, Or, and Not, such as IF(AND()), IF(OR()), and IF(NOT(). These combinations do not work if you substitute SWITCH for IF.
The sample spreadsheet uses the SWITCH function to format prices in columns B through G using the TEXT function and the results of the SWITCH function from column O.
Columns N and O can be hidden.
The user can change any symbol and the prices will use the correct decimal formats.
Requirements: CQG Integrated Client or QTrader, and Excel 2016 (locally installed, not in the Cloud) or more recent.