6 Replies Latest reply on Mar 7, 2014 10:54 AM by philmodjunk

    Summing up fields



      Summing up fields


           If I have a field whose data I want as the sum of other fields, I understand that I will have to set it as a "Calculated Value" and simply add all of the fields that I want summed in, however what if I wanted to incorporate an if statement. For example say I had 4 fields, A, B, C and D. I want D = A+B+1, the last 1 corresponds to if the data in C is greater than or equal to 65 

        • 1. Re: Summing up fields


               Not entirely sure what you want, but I'm guessing that you need a calculation for fieldD something like:

               If (fieldC >= 65 ; Sum(fieldA ; fieldB ; 1) ; Sum(fieldA ; fieldB) )

               which is just saying that if fieldC is greater than or equal to 65, set fieldD to fieldA + fieldB + 1 , otherwise set fieldD to fieldA + fieldB


          • 2. Re: Summing up fields

                 If all your fields are defined in the same record:

                 A + B + If ( C > 65 ; 1 )

                 This assumes that you don't want to add anything if C > 65. This could be simplified to be:

                 A + B + ( C > 65 )

                 This works because Boolean expressions that evaluate as True return the number 1 and if False return 0.

            • 3. Re: Summing up fields

                   Thank you, now say I already had data in field D that was incorrect. How would I go about correcting the data in field D now that I have set it's calculation to A+B+(C>=65), that is overriding the current data in field D. Currently, the calculation will not update the current data in field D

              • 4. Re: Summing up fields

                     You would not correct the data in Field D. If Field D shows the wrong data, the error is due to wrong data in fields A, B or C. You would correct the data in one of these fields so that your calculation can update to show the correct result.

                • 5. Re: Summing up fields

                       I see, but say before the calculation was done, field D =3, even though A+B+(C>=65) was =2. Once I set up the calculation, D does not update to 2, it stays as 3

                  • 6. Re: Summing up fields

                         IF D is a calculation field, it will update automatically.

                         If D is a number field with an auto-entered calculation, it may or may not update automatically. If fields, A, B and C are fields in the same record and you clear the "do not replace existing values" check box, changing the value of A, B or C will cause the calculation to update.

                         And adding/changing an auto-entered calculation will not change the data in existing records, only new records or records where one of the referenced fields is modified. To update all records in your table when you add/change an auto-enter calculation, see this thread: Updating values in auto-enter calc fields without using Replace Field Contents