Alpha Video Training
Results 1 to 13 of 13

Thread: Average

  1. #1
    Member
    Real Name
    Edward Mattison
    Join Date
    Jun 2002
    Posts
    208

    Default 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. #2
    "Certified" Alphaholic
    Real Name
    Charlie Crimmel
    Join Date
    Apr 2000
    Location
    West Virginia
    Posts
    1,695

    Default 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

  3. #3
    Edward F. Schulz
    Guest

    Default 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

  4. #4
    Edward F. Schulz
    Guest

    Default 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

  5. #5
    Edward F. Schulz
    Guest

    Default RE: Average

    Oops, that doesn't work
    efs

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default 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


  7. #7
    "Certified" Alphaholic
    Real Name
    jim chapman
    Join Date
    Apr 2000
    Posts
    1,779

    Default 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

  8. #8
    VAR Dan Blank's Avatar
    Real Name
    Dan Blank
    Join Date
    Apr 2000
    Location
    Fort Worth, TX
    Posts
    995

    Default 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.

  9. #9
    Edward F. Schulz
    Guest

    Default 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.

  10. #10
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default 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)

  11. #11
    Member
    Real Name
    James Urbanski
    Join Date
    May 2000
    Location
    New Port Richey, FL
    Posts
    620

    Default 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

  12. #12
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default 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

  13. #13
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default 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

Similar Threads

  1. Monthly average
    By edward.mattison@scbhn.org in forum Alpha Five Version 6
    Replies: 7
    Last Post: 08-19-2005, 12:52 PM
  2. Count and Average Q
    By seidel1 in forum Alpha Five Version 5
    Replies: 5
    Last Post: 06-03-2004, 08:34 AM
  3. Average Function.
    By Sharon Bracken in forum Alpha Five Version 5
    Replies: 6
    Last Post: 08-08-2002, 11:03 AM
  4. filtered average
    By Leonard Rosenstein in forum Alpha Five Version 4
    Replies: 4
    Last Post: 01-03-2002, 03:03 PM
  5. Average function
    By Bernard Posner in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-02-2001, 04:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •