Summary Fields (Average)

[fmsavey]

I need to calculate the ratio between two numbers, A and B, for each record and the average for multiple records. The ratio calculation is R = A / B. I created a summary field to get the average ratio and weighted it by the B column. I also created summary fields to get the totals for A and B. In the example below I have three records showing the values for each column, the last record has no value for field B.

A, B, R

30, 5, 6

15, 2, 7.5

15, 0, ?

The summary fields in FM shows.

60, 7, 6.43

What can I do to get FM to consider that last record that has no value for B and give an average ratio of 8.57 ( 60 / 7 = 8.57 )on the summary line?

Thanks in advance for your help!

[Ender]

Since division by 0 cannot be evaluated, what would you have it be? If it should be 0, have your ratio give it a 0, if it should be blank, don't give it a value:

R (calculation, number result) = case(B<>0; A/:

This may solve the problem, as I think a summary average only factors in records with values (if that's what you wanted.)

[fmsavey]

For record 1 the ratio is 30/5 = 6. For record 2 the ratio is 15/2 = 7.5. For record 3 the ratio of 15/0 is not evaluated. When all three records are totaled the ratio should be 60/7 = 8.57. However filemaker ignores record three and shows the average as 6.47. I was hoping someone could tell me what weight I could use to get the result of 8.57. I know this weight would have to be a calculated field.

[Ender]

You'll have to calculate the average of the total then. Try

SumAverage (calculation, number result) = getsummary ( TotalA ) / getsummary ( TotalB )

where TotalA and TotalB are summary totals of A & B.

[Ender]

Oops, you'll need a break field in those getsummary()'s. Whatever you're sorting/grouping by should work. Or if you're working with totals on the entire found set, then the getsummary() can be skipped and the summaries used directly:

SumAverage (calculation, number result) = TotalA / TotalB

[comment]

Something doesn't add up here (pardon the pun).

First you said you want the average ratio weighted by the B column.

Now it looks like you want the average of A, weighted by B (which actually would make more sense to me).

The average of A, weighted by B, is calculated like this:

Sum ( A * B ) / Sum ( B )

Using your example:

(30*5 + 15*2 + 15*0 ) / ( 5 + 2 + 0 ) =

180 / 7 =

25.71

If you define a summary field as Average of A, weighted by B, that is what you'll get. The individual ratios are of no concern here.

However, the actual calculation that you show is yet another thing. I don't know exactly what to call the result you want (ratio of totals?), but the calculation is quite simple, as Ender has shown.

I am not sure what the result of this calculation means, though.