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

    Simple calculation

    ms.15

      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
          LaRettaK

          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
            Nehme

            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
              johan

              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
                alquimby

                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
                  johan

                  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
                    alquimby

                    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
                      LaRettaK

                      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.