=AGGREGATE(1,4,range)
=AGGREGATE(1,5,range)
=AGGREGATE(1,5,tbl_Events[Days since last Referral for this patient])
=AVERAGE(tbl_Events[Days since last Referral for this patient])
=SUBTOTAL(1,tbl_Events[Days since last Referral for this patient])
=IF([@Event]="Referral","",IFERROR([@[Event Date]]-1/(1/AGGREGATE(14,6,[Event Date]/([Event]="Referral")/([Patient Number]=[@[Patient Number]])/([Event Date]<=[@[Event Date]]),1)),""))
=SUBTOTAL(1,[@Days since last Referral for this patient])
=IF([@Event]="Referral","",IFERROR([@[Event Date]]-1/(1/AGGREGATE(14,6,[Event Date]/([Event]="Referral")/([Patient Number]=[@[Patient Number]])/([Event Date]<=[@[Event Date]]),1)),""))
Sorry I misunderstood.No no no, if you look at the SUBTOTAL formula, you will see that I removed the table name and added an @ symbol in order to validate each row of the table individually.
Even if there are no errors at present, this might help to identify the cause of the errors in the subtotal formula.
=SUBTOTAL(1,[@[Days since last Referral for this patient]])
=AGGREGATE(1,4,[@[Days since last Referral for this patient]])
=AVERAGE([@[Days since last Referral for this patient]])