I have a Virtual Column, and in the App Formu...

AppSheet AppSheet Q&A
1

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

2

@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” )

3

@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

4

@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!

5

@tony Good point. Cheers!