While the IF function has been around for a while now, IFS is new to Excel. This function checks whether one or more conditions are met and then returns the value associated with the first TRUE condition. The main objective of IFS is to simplify an expression that would have previously required multiple nested IF functions.
The general format of the IFS function is
| =IFS(Condition 1, Value if Condition 1 = TRUE, Condition 2, Value if Condition 2 = TRUE, Condition 3, Value if Condition 3 = TRUE) |
The function can handle up to 127 different conditions, and only the first two parameters are required.
Here is an example using a sample Excel grid:
| =IFS(A1>250,A2,B1>250,B2,C1>250,C2,D1>250,D2) |
will return “Green” (the value of cell C2).
In older versions of Excel, this would be written as
| =IF(A1>250,A2,IF(B1>250,B2,IF(C1>250,C2,IF(D1>250,D2)))) |
Multiple nested IF functions would previously be used to accomplish the same result.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip!
Read related posts with Excel Tips & Tricks and using Excel with Planning Analytics:
IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP
IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula
IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets
