I have a Virtual Column, and in the App Formula, I want IFS() statements. These are to return values based on 3 different conditions, one of which has 3 options, the second has 3 options, and the third has 7 options.
So I used AND() to evaluate these conditions, but I need 63 IFS() statments (I thinkโฆ 3 x 3 x 7) When I get to 44 IFS() statments, the formula is Valid. When I add the 45th, it is not Valid, or rather, it shows a little red cross, but no reason for failing.
Am I limited in the number of conditions in an IFS() statement?
If so, is there any other way I can arrange this?
Thanks
@Green_Gorilla It sounds like thereโs probably a better way to do this. While there may be 3 * 3 * 7 = 63 different possible combinations of values, Iโm guessing that most of the combinations behave in exactly the same way and can be expressed in a concise way.
Iโd start working backwards. What are the possible output values of your formula? For each output value, write down the condition(s) that would result in that output value. Then, combine those into an IFS expression.
For example:
IFS( AND([Color] = Red, OR([Size] = Medium, [Size] = Small)), โMachine Number Oneโ, [Color] = Blue, โMachine Number Twoโ, [Size] = Large, โMachine Number Threeโ, โNo Capable Machineโ )
Compare that to the more verbose: IFS( AND([Color] = Red, [Size] = Small), โMachine Number Oneโ, AND([Color] = Red, [Size] = Medium), โMachine Number Oneโ, AND([Color] = Red, [Size] = Large), โMachine Number Threeโ, AND([Color] = Blue, [Size] = Small), โMachine Number Twoโ, AND([Color] = Blue, [Size] = Medium), โMachine Number Twoโ, AND([Color] = Blue, [Size] = Large), โMachine Number Twoโ, AND([Color] = Yellow, [Size] = Small), โNo Capable Machineโ, AND([Color] = Yellow, [Size] = Medium), โNo Capable Machineโ, AND([Color] = Yellow, [Size] = Large), โMachine Number Threeโ, โNo Capable Machineโ )
@tony I may be able to do that, that seems like a good idea.
I have solved it myself for now, but it is less elegant. Just split them into 3 columns, and then selected whichever of the three dependent on one of the variables. It works, but like I said, less elegant.
I will get around to tarting it up shortly.
Thanks
@Green_Gorilla That sounds totally fine! What you want to avoid is the exponential blowup of your formulaโฆ you donโt want to add a new possible value and have to make your formula 63 lines longer than it was!
@tony Good point. Cheers!
User | Count |
---|---|
44 | |
28 | |
28 | |
21 | |
13 |