The ?( function allows a formula to make a true/false, yes/no decision.
This function has three required parameters:
iftest – A true false value that controls the decision of the formula.
truevalue – The function result if the iftest parameter is true.
… – The function can have additional iftest/truevalue parameters inserted here. The function will evaluate each iftest/truevalue pair in order from left to right. The falsevalue parameter will only be used if all of the iftest parameters are false.
falsevalue – The function result if the iftest parameter is false. If multiple iftest parameters are supplied this value will be the function result only if all of the iftest parameters are false.
This function returns the value of either one of the truevalue parameters or the falsevalue parameter, depending on whether the iftest parameter (or parameters) is true or false. The truevalue and falsevalue may calculate numbers or text, but usually all of the value parameters should calculate the same type of data (otherwise the rest of the formula will probably produce an error depending on which choice is selected).
?(1=1,"same","different") ☞ same ?(1=2,"same","different") ☞ different
You can add additional pairs of iftest/truevalue parameters to the function. In that case, it will evaluate each pair from left to right. If none of the iftest parameters are true then the final falsevalue parameter will be selected.
?(5=5,"same",5>5,"bigger","smaller") ☞ same ?(5=4,"same",5>4,"bigger","smaller") ☞ bigger ?(5=6,"same",5>6,"bigger","smaller") ☞ smaller
No matter how many truevalue and falsevalue parameters are included in the function, ultimately only one is actually used. The rest are effectively discarded. Since these extra parameters are not actually used, Panorama doesn’t require that they have valid values. In other words, if an unused parameter contains a runtime error, Panorama doesn’t care. Since the value isn’t used, the formula continues normally without stopping because of the error.
For example, suppose that you have a division formula like this:
This is fine for most values, but what if the denominator is zero? Division by zero is invalid. Using the ?( function we can ensure that a valid result is calculated even if the denominator is zero (assuming you think zero is an acceptable result in this situation).
If the denominator is zero the division is ignored, and zero is returned.
Advanced Notes: 1) Experienced Panorama users will realize that the formula above would not work in earlier versions of Panorama, because the division error would not be ignored. However, this now works fine in current versions. 2) Strictly speaking, division by zero does not produce an error in the current version of Panorama (it did in previous versions). Instead of an error, division by zero now produces the value infinity.
Tip: As an alternative you can spell this formula ifelse(.
ifelse(1=1,"same","different") ☞ same ifelse(1=2,"same","different") ☞ different
Note for C programmers: The ?( function is similar in operation to the C ?: operator. For example, in C you would say:
a ? b : c;
In Panorama this would be expressed like this:
?( function is missing the final false value. – This function must have an odd number of parameters (3, 5, 7, 9, etc.). If it has an even number of parameters then this error will occur.
|10.0||Updated||This function has several major changes from previous versions. |
1) Previously this function only allowed 3 parameters. Now it allows 3, 5, 7, 9, etc. You can add as many iftest/truevalue pairs as you need. The final falsevalue parameter will only be used if *all* of the iftest parameters are false.
2) Previously Panorama evaluated all of the parameters of the ?( function. If an error occured in any parameter, that error would "blow up" the entire formula. Now errors in truevalue and falsevalue parameters are ignored if that parameter is not actually used.
3) You can now use ifelse( as an alternative spelling for ?(.