I have a calculation field (GreenOverloadLight) that changes its container results based on the value of another calculation field (deviation).

It works fine when "deviation" contains a number but sometimes it contains a question mark and I don't know how to reference that condition in a calculation.

I have tried "= "?"" and "> "0"" but no luck.

Thanks

In the calculation for deviation, you might want to confirm that "result is a number" is selected (not result is text). Although FileMaker tries to build in the flexibility for a text field to hold a number, it doesn't seem to cover all possible cases. I recently had "?" appear in some calculated fields and I traced it to the text/number selection.

I checked it out and it is a number.

I also checked out each of the fields referenced in the formula (see attachment) and they are all numbers as well.

In either case, "?" is still something. Is there a way to include that condition "?" in a formula anyway?

Yes, "?" is something - it is an indication of an error in evaluating your formula. The best way to fix this is to fix the formula, so that it always returns a valid result. But this test:

Deviation = "?"

should also work.

Thanks, I am taking your advice and trying to fix the forumula which is:

(new_c_AverageScoreForStandard_forSelectedYear - current_mean) / mean_point_value

I notice that "mean_point_value" is generating a "?".

Question: How do I get "mean_point_value" so that it doesn't generate a "?"

It's formula is:

100/goal_1

Both "goal_1" and "mean_point_value" have results set to numbers and both are told not to evaluate if all reference fields are empty. "goal_1" has an auto entry of "0" and I just refilled all "0" fields to make sure there was nothing extra in there.

I am guessing you get an error when goal_1 is empty or zero - since you cannot divide by zero. So probably you'd want something like:

Case ( goal_1 ; 100 / goal_1 ; )

Thanks Vetran,

I ended up having to apply your suggestion to both fields because "?" was still showing up in another formula. I then used the final arbitrary number in the calculation that gave me control over the calculated container field I was originally trying to work with.

Lol Michael,

I am so calling you veteran from now on. Done deal.

Think twice about the price...