7 Replies Latest reply on Nov 23, 2015 9:19 AM by LaRettaK

# Simple calculation

I have four fields. Fields 1-3 are Number Fields containing values.   In Field 4, which is a calculation field, I want a new value:  Field 1 value (if 2 and 3 are empty)  or  Field 2 value / 4 (if Field 1 and 3 empty)  or  Field 3 value / 12 (if Field 1 and 2 empty)  How can this be done?

• ###### 1. Re: Simple calculation

Here is one way:

Case (

not Count ( Field2 ; Field3 ) ; Field1 ;

not Count ( Field1 ; Field3 ) ; Field2 / 4 ;

not Count ( Field1 ; Field2 ) ; Field3 / 12

)

But have you covered all your bases?  What if only Field2 has a value? Or 1 and 2?  Because if these are the only configurations - if a value only exists in ONE of the three fields then this would work as well:

Field1 +

Field2 / 4 +

Field3 / 12

• ###### 2. Re: Simple calculation

Case (

IsEmpty ( Field 1 ) and IsEmpty  ( Field 2 ) ; Field 3 / 12 ;

IsEmpty ( Field 2 ) and IsEmpty  ( Field 3 ) ; Field 1 ;

Field 2 / 4

)

• ###### 3. Re: Simple calculation

Here is another way, using the oft forgotten Xor function:

( Field1 xor Field2 xor Field3 ) * ( Field1 + Field2/4 + Field3/12 )

The first part is True (or 1) if and only if exactly one of the three fields is non-empty and non-zero. That is why we can add all of the fields in the second part.

This is by far the most difficult formula to understand of those suggested so far. I would recommend Nehme's suggestion if you ever want to review your calculation.

• ###### 4. Re: Simple calculation

Johan,

"( Field1 xor Field2 xor Field3 ) * ( Field1 + Field2/4 + Field3/12 )

The first part is True (or 1) if and only if exactly one of the three fields is non-empty and non-zero"

You also get a True if all 3 fields are non-empty and non-zero.

Al Quimby

• ###### 5. Re: Simple calculation

True. And that is why I would not recommend my solution, it is a bit too difficult to correct.

Although this would help:

not ( Field1 and Field2 and Field3 ) * ( Field1 xor Field2 xor Field3 ) * ( Field1 + Field2/4 + Field3/12 )

I repeat, this is not the most obvious code. Only do it this way if you are sure that someone you hate will do all further development.

• ###### 6. Re: Simple calculation

This works: Max (field1 ; field2 / 4 ; field3 / 12 ), IF 2 and 3 are empty if 1 is not, 1 and 3 are empty and 2 is not, and 1 and 2 are empty and 3 is not.

Al Quimby

• ###### 7. Re: Simple calculation

Another issue not being taken into account ... we are all working from the assumption that the fields are empty or have an integer.  However, what if the field contains 0?  Count() and IsEmpty() will produce false positives.

What also works:

Case (

not Field2 + Field3 ; Field1 ;

not Field1 + Field3 ; Field2 / 4 ;

not Field1 + Field2; Field3 / 12

)

This would treat empty and 0 fields the same.