After a brief hiatus, the Function of the Week is back and just as ordinary, normal, typical, standard and regular as ever! Why so mundane? Because we’re picking apart the Average function this week and we’ve caught the Average Spirit! Average might not be new or sexy, but there are three variations in its application and if you apply them incorrectly, you may end up with below-average satisfaction.
Averaging Multiple Fields
In this example, we have three fields named numberA, numberB and numberC. We didn’t have to stop at 3 fields; the Average function allows us to string together as many fields as we like. When evaluated, FileMaker will add up all the non-blank values in those fields and divide by the number of values it found. Non-blank is a critical feature of how Average computes. Take a look a the examples below.
The top example above works exactly as you’d expect an Average function to operate. Each of the three fields contains the value 2. FileMaker adds them together to get 6 and divides by 3 (the number of values present) to derive an average value of 2.
In the middle example, one of the three fields is blank. So the sum of the three fields is now 4. But 4 divided by 3 is (approximately) 1.33 and FileMaker is insisting that the average of the three fields is still 2. This is where non-blank comes into play. FileMaker is still dividing the sum by the number of values present and because one of the three fields is blank, only two values are present. Thus 4 ÷ 2 = 2.
The bottom example shows when FileMaker would return an average of 1.33. Now, instead of being empty, the numberC field contains a zero. With three values once again present, FileMaker divides 4 by 3.
The non-blank stipulation wasn’t invented by FileMaker. A fundamental tenet of mathematics is that zero is not the same as nothing. While scholarly papers have been written on the topic, this Difference Between Article boils is down nicely. If you care to go a little deeper, Stuff You Should Know has a great podcast about zero.
Averaging Related Records
If you toss a single related field to Average, it’s going to give you the average of the non-blank values in all the related records. A valid relationship and a number field (or calculation field with a number result) are all that is necessary to calculate the average. The related field does not have to appear in a portal or even on a layout at all. If you do happen to have a filtered portal displaying some, but not all, related values, keep in mind that FileMaker will always calculate the average based on all the related records. Filtered portals do not affect the calculation result.
As soon as you give Average more than one field, and a combination of local and related values like the example above is perfectly acceptable, FileMaker reverts to averaging multiple fields. It will no longer average all the related values. Instead, in the example above, FileMaker will take the first related value for relatedTable::numberField, add that to the value for numberA in the current record, and compute the average of those two.
Averaging a Repeating Field
Your humble blogger is of the opinion that repeating fields are an anachronism from FileMaker’s pre-relational days, now two decades behind us. Many developers whom I know and respect disagree with me on this point and it certainly won’t be resolved in a blog post about the Average function. But the function, like the rest of FileMaker, still supports them, so here’s what you get.
With a single repeating field for your Average parameter, FileMaker will average all the non-blank values appearing in the repetitions as illustrated below, very much akin to averaging a single related field.
If you want to average across multiple repeating fields, you can do so by creating a new calculation field and inserting your repeating fields as the parameters of an Average function. Be sure to set the calculation to contain the same number of repeats as the number fields you’re averaging. An example of the output appears below.
Do this is you want to make me cry.
Like so many FileMaker functions, Average is superficially quite simple. Knowing the vagaries of how it’s implemented will help you be successful in using it.
Subscribe to our blog feed