6 Replies Latest reply on Nov 28, 2012 2:07 PM by jrichardson

    Sum and average numbers in same field

    jrichardson

      I am not the sharpest tac in the box, but I cannot for the life of me get a calculation. Maybe someone can help?

       

      I would like to have a field where I can enter multiple numbers that will add together than give me the average of those numbers.

       

      example: 1 2 3 = 6 and the average is 2.

       

      Can this be done in one field or do I need a field that will sum the numbers and another field that will do the average of the sum?

       

      Thanks for any help!

        • 1. Re: Sum and average numbers in same field
          comment

          jrichardson wrote:

           

          Can this be done in one field or do I need a field that will sum the numbers and another field that will do the average of the sum?

           

          It can be done in one field, but the result must be of type Text - thus useless for anything other than being displayed.

           

           

          jrichardson wrote:

           

          I would like to have a field where I can enter multiple numbers

           

          Hopefully you will be entering each number in it own  record - otherwise it won't be so easy.

          • 2. Re: Sum and average numbers in same field
            jrichardson

            Michael,

             

            Thanks so much for the quick response!

             

            I do need to enter all numbers in one record in one field to sum.  I don't necessarily need to have the average in the same field.  I can create a new one for the average.

            • 3. Re: Sum and average numbers in same field
              comment

              jrichardson wrote:

               

              I do need to enter all numbers in one record in one field to sum.

               

              Why is that? It is simply not a good starting point. Filemaker is designed to process structured data. A bunch of numbers in a field is just a meaningless piece of text. You could tell Filemaker to substitute the delimiters with a "+" sign and evaluate the result to get the sum - but it's a clunky (i.e. easy to break) solution.

              • 4. Re: Sum and average numbers in same field
                jrichardson

                We run about 1-200 classes with 5-10 students in each class per year and have each student fill out an evaluation with a number rating system.  These evaluations are then input into the database by class.  At the momement we are having to total and average each sum in an excel spreadsheet than transfer the data to the database.  Total waste of time.  If we could skip the spreadsheet and have FMP do the calculations it would save a lot of data entry time.

                 

                I know what you are going to say.  Why dont we have the evaluation online?  Don't have the manpower yet to facilitate creating an online evaluation for our students.  The system we have now will have to do until we can get some grant money (if we can find any) to update our system.

                • 5. Re: Sum and average numbers in same field
                  comment

                  jrichardson wrote:

                   

                  I know what you are going to say.  Why dont we have the evaluation online? 

                   

                  No, what I am saying is that whoever is doing the data entry should press tab in between the numbers to move the cursor to the next portal row (or at least to the next repetition of a repeating field). Then the whole thing becomes trivial (see an attached example)

                  • 6. Re: Sum and average numbers in same field
                    jrichardson

                    Oh, I understand what you mean.  I think I just have to do a little adjustment and I think I am good.  Thanks for all your help!!!