Jump to content

Summary Fields (Average)


This topic is 5762 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5762 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.