I got asked by a friend to create a formula that will pass a value from 1-4 to a parameter called IP. The 1,2,3, or 4 will come from one of 4 Yes/No parameters (LB, LC,LF, and LO). As a given check-box is checked "on", the other check-boxes should turn off and the value set into the IP parameter.
Here is what I came up with...
Users can check as many boxes as they like under Constraints, but if they check more than one the formula that IP is set to will return a zero, letting them know multiple are checked.
The formula for IP first checks for every bad situation of multiple checking that is possible. If any are true it sets IP to BADpick or zero. Once multiple picking is eliminated, it checks for the four appropriate single checks, and will return a 1,2,3, or 4 respectfully. If neither a good or bad pick is true it defaults to zero. The formula checks for bad outcomes first because as soon as it finds a true response, it stops checking.
The hard part might have been quantifying all the bad responses. I start by trying to come up with each combination of two checks.
LB and LC
LB and LF
LB and LO
LC and LF
LC and LO
LF and LO
With four possible choices, you know you have all the combinations when each shows up 3X in your list. I did the same for combinations of threes, again ensuring each parameter showed up 3X.
LB and LC and LF
LB and LC and LO
LB and Lf and LO
LC and LF and LO
Lastly, I check for all four parameters checked at once.
LB and LC and LF and LO.
This is the entire formula. I used if/and statements
if(and(LBTrue, LCTrue), BADpick, if(and(LBTrue, LFTrue), BADpick, if(and(LBTrue, LOTrue), BADpick, if(and(LCTrue, LFTrue), BADpick, if(and(LCTrue, LOTrue), BADpick, if(and(LFTrue, LOTrue), BADpick, if(and(LBTrue, LCTrue, LFTrue), BADpick, if(and(LBTrue, LCTrue, LOTrue), BADpick, if(and(LBTrue, LFTrue, LOTrue), BADpick, if(and(LBTrue, LCTrue, LOTrue, LFTrue), BADpick, if(LBTrue, LB, if(LCTrue, LC, if(LFTrue, LF, if(LOTrue, LO, 0))))))))))))))
It's definitely blunt force programming, but it does the trick to make sure only one in four is picked.