Formula for average of the number of studies ordered by a doc, per patient encounter on a given date

[happy3sum]

I am trying to figure out a formula that calculates for the average number of studies ordered by a doc, per patient encounter on a given date. 'Per Patient Encounter' is defined as each specific date that the DOC saw the PATIENT which counts as (1) encounter.

Here is an example of my pseudo data:

DATE EXAM ID DOC NAME PATIENT ID AVERAGE EXAMS ORDERED PER PATIENT ENCOUNTER
1/1/13 001 SMITH 88888
1/1/13 002 SMITH 88888 2 (since SMITH ordered 2 exams for PAT ID 8888 on 1/1/13)
1/2/13 003 JONES 99999
1/2/13 004 JONES 99999 2 (since JONES ordered 2 exams for PAT ID 9999 on 1/2/13)
1/3/13 005 SMITH 88888 1 (since SMITH ordered 1 exam for PAT ID 8888 on 1/3/13)


Final total result I would come up with in this example is:

SMITH (AVG EXAMS ORDERED PER PAT ENCOUNTER is 1.5 (3 exams ordered / 2 encounters)
JONES (AVG EXAMS ORDERED PER PAT ENCOUNTER is 2 (2 exams ordered / 1 encounter)

Assistance is GREATLY APPRECIATED!

 

[Gerald Higgins]

If the 2, 2, and 1 in your source data table is already being calculated somehow, then you can use a Pivot Table to calculate the overall averages.

PS - welcome to the board !