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

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

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

5. RE: Average

Oops, that doesn't work
efs

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.

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

Posting Permissions

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