Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Average

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Average

    I want to create a field that is the average of 6 other numeric fields in the same record. Some of the fields may be blank. What is the right expression. Just adding the six fields and dividing by six doesn't work, if some of the fields are empty. Thanks Ed

    #2
    RE: Average

    I would set up 7 calculated fields in the table.
    calc1
    calc2
    calc3
    calc4
    calc5
    calc6
    calc7
    this might be over kill but it is a easy way to do it.
    assume that field1 is one of your fields that you are checking and that it is a numeric field.

    the calculated expression could be for the first calculated field would be.
    if(field1>0,1,0)
    the second-calc2
    if(field2>0,1,0)

    do this 6 times for each field that you want to check.
    the seventh calc field would be
    calc1+calc2+calc3+calc4+calc5+calc6
    this will give you the amount of columns. you can use this field to divide by.

    charlie crimmel

    Comment


      #3
      RE: Average

      I would use a while loop to add each field to a calc field then divide that value
      by the i or n value from the loop.
      efs

      Comment


        #4
        RE: Average

        I forgot to mention to check each field for >0 in the while loop as well as adding it to the calc field
        efs

        Comment


          #5
          RE: Average

          Oops, that doesn't work
          efs

          Comment


            #6
            RE: Average

            Edward, you'll be well served to avoid blank numeric fields. If there's no value there, force a zero into the field.

            I'll think about your average calc expression, but first thought is that you're facing a difficult problem. Consider if you were doing this for only two fields, either of which might have data.

            If both are empty the average is zero.
            If both are full, the average is the sum, divided by two.
            If the first is empty but the second is full, the average is the value of the second one.
            If the first is full, but the second is empty, the average is the value of the first one.

            There you see it. For two fields, you have two to the second power possibile arrangements, all of which must be provided for.

            Your case, is exponentially worse.

            If each of six fields could either have data or not, you've got two to the sixth power of possible arrangements (64 possible arrangements of the fields ranging from all empty to all full, and any combination in between.) All of these possibilities would have to be dealt with in the expression. I'm afraid you'll better off restructuring the record, putting each in a separate table. Databases work like blazes from top to bottom, but, unlike spreadsheets, are pretty clumsy when doing arithmetic left to right within the same row.

            If the values were spread across 6 tables you could use a script (not just an expression) to retrieve the values from the six tables, adding them together, and keeping track of how many values were retrieved. The average is trivial to compute, and could be stored in a seventh table.

            Hope I'm wrong, but any brute force solution that I can conceive will be very, very, messy, or down right impossible within the expression length limits of Alpha Five, so long as leave them all on one row in a single table.

            -- tom

            Comment


              #7
              RE: Average

              Hello Ed,

              Uninitialized numerical fields can have some bizare values, usually unrealisticly high values. I think this is because of garbage in uninitialized memory locations. This is and has been an issue for many programming languages. Many requiring you to initialize varibles before you use them. I mention this just to dispell the thought that this is a bug in Alpha. In a script if I have the potential of running into this issue, I will check and make sure the value is greater than .0001 and less than 1000000000.

              I haven't tried this, but it may work. Set a default value in the field rules of all the fields to 0. Set it to default at the beginning of record entry.

              Jim

              Comment


                #8
                RE: Average

                What about setting up a set where you have the parant record with the main data, And then you have a child via a one to many link.

                The numbers get entered into the child, then do an average function for the numbers in the child that match the parent record.

                Haven't tried it but it should work. You can even store the value in the Parent table.

                Dan.
                Dan

                Dan Blank builds Databases
                Skype: danblank

                Comment


                  #9
                  RE: Average

                  Second thawt. Using a while loop to add the values of the fields to average it might work if you place "if field=0,i=i-1,i=i+1". Then divide the total by i. This assumes that the default for the field is 0, not blank.

                  Comment


                    #10
                    RE: Average

                    Other good suggestions here, but if you mean something like six potential test scores and if it's blank, don't count it in the average because it hasn't taken place yet, you should be able to do it with several calc fields fields. Here's what I'd try if I had two potential scores

                    Testtimes = IF(ISBLANK("Test1"),0,1) + IF(ISBLANK("Test2"),0,1)
                    Testtotscore = IF(ISBLANK("Test1"),0,Test1) + IF(ISBLANK("Test2"),0,Test2)
                    Testaverage:IF(CALC->Testtimes > 0,CALC->Testtotscore/CALC->Testtimes,0)

                    Comment


                      #11
                      RE: Average

                      I do this a great deal with my class room grade calculations. I use one field the score and a second field as a counter. The formula for the counter field is IF(ISBLANK("Score"),0,1), "score" is the field holds the value for a test. I also use an identifier for each score (test_ID). There are two calculated fields, one adds the total of the score fields and the second calculates the value of the counter fields. The last field is a calculated field to find the average (total score/total counter). If this isn't clear, e-mail me and I'll try to send a stripped down version without semsitive data to you.

                      Regards

                      Jim

                      Comment


                        #12
                        RE: Average

                        Just to add to Jim's response, if this is only needed in a report you should even need an extra table field. A calculated counter field in the report should do just as well.

                        Ray

                        Comment


                          #13
                          RE: Average

                          Jim, thanks. I can see how such a thing would work in a report. Are you saying it produces a running average in each record, in real time, too?

                          -- tom

                          Comment

                          Working...
                          X