Newbie Help WIth Average Function

[Lee Raney]

First off I am a newb, so go easy.

We use Filemaker for our scheduling of orders in a screen printing business.

All orders are entered into schedule in a list format. One of the fields is GSP (Gross Sales Profit). Each order is given a GSP based on a formula we determined.

What I have been asked to do is get a daily GSP for each Dept on orders entered. I create a report each day based on a CTRL Find for that date. Then I sort by Departments. I would create 5 fields with this for each dept to have on a daily report.

I created a summary field that totals the number of orders per dept each day. But i cant seem to figure out how to make a new field with a calculation that is basically - If Dept = "A" then AVG GSP / Total Orders .

Any help would be greatly appreciated, and thank you in advance.

[comment]

I would create 5 fields with this for each dept to have on a daily report.

.

I believe ONE field should be quite sufficient for all 5 departments - but I didn't quite understand the required calculation.

[Lee Raney]

Sorry, when you dont know the words its hard to articulate yourself properly.

I have attached a screen shot of the report I am creating.

As you can see its a line by line of our orders that shipped on 3/4. I have a formula that calculates the GSP (Our profit) for each order.

At the bottom I have the 5 Departments (Auto, Manual, Awards, Embroidery and AD Specialty)I have created a summary that counts up all the order per dept.

What I would like to do is have a field for each dept in the dept boxes below that takes all the Auto Orders , in this pictures case 11 of them , add all the GSP's and then divide by 11 to get an average GSP for Auto , and so on for each other dept. I get the found records by doing a manual CTRL F for that day so I dont need a date range of any kind in this.

[comment]

Are you married to this format, or can you change it? Filemaker's "native" way is to attach the sub-summary values to the group of records being summarized, e.g.

Department: Auto

• Order#: 123, Amount: 110

• Order#: 145, Amount: 200

• Order#: 167, Amount: 320

Orders: 3, Total: 630, Average: 210

---

Department: Manual

...

To do it the way your pic shows is more complex.

[Lee Raney]

Married, its part of several tables with same format, My sales group is avg age of 50 + who cant remember to hit CTRL N. Drastic changes are not an option.

Was hoping for a simple calculation with an If statement or something like that, im guessing its not going to be that simple.

[comment]

Well, you could split the values into a repeating calculation field with 5 repetitions, then use a summary field to summarize each repetition individually (I presume the structure of 5 departments is not likely to change often).

avg age of 50 + who cant remember to hit CTRL N.

Careful there, youngster. I could probably view this as ageism - if I could only find my reading glasses...

BTW, they should not be required to remember anything. It's your job as the developer to provide them with buttons and/or menus to make it easy for them.