Unable to create an ARRAYFORMULA that will AVERAGE the results of a QUERY

Question

I have a sheet data that tracks client transactions.

1. Each row is a transaction
2. The columns are:
   • LAST NAME
   • PHONE NUMBER
   • SUB
   • TOTAL
   • TIP
   • PERCENTAGE
3. The TIP and PERCENTAGE columns are calculated based on the information entered in the SUB and TOTAL columns.

I am trying to write a formula in another sheet average that returns the average tip percentage for each client based on their transactions. Clients are uniquely identified based on their LAST NAME and PHONE NUMBER fields.

Here's a sample of the data sheet before transformation:

LAST NAME	PHONE NUMBER	SUB	TOTAL	TIP	PERCENTAGE
Smith	(123) 456-7890	$28.66	$32.96	$4.30	13.05%
Johnson	(987) 654-3210	$58.68	$68.68	$10.00	14.56%
Williams	(555) 123-4567	$35.48	$40.48	$5.00	12.35%
Jones	(111) 222-3334	$52.22	$55.00	$2.78	5.05%
Smith	(123) 456-7890	$50.82	$70.82	$20.00	28.24%
Johnson	(987) 654-3210	$51.23	$58.68	$7.45	12.70%
Williams	(555) 123-4567	$33.47	$35.47	$2.00	5.64%
Jones	(111) 222-3334	$44.27	$50.91	$6.64	13.04%

Example result in the average sheet using the sample above:

LAST NAME	PHONE NUMBER	PERCENTAGE
Smith	(123) 456-7890	20.64%
Johnson	(987) 654-3210	13.63%
Williams	(555) 123-4567	9.00%
Jones	(111) 222-3334	9.05%

The following formula didn't work:

=ARRAYFORMULA(
   IF(A2<>"", AVERAGE(QUERY(Data!A2:F, 
     "SELECT F WHERE (A='"&A2&"' AND B CONTAINS '"&B2&"')")),
     IFERROR(1/0)))

I'm considering using VLOOKUP but need help with the implementation.

I would to use an array formula placed in the top cell of the average sheet that will spill down and update automatically whenever transactions are added.

To provide more context and facilitate the process, I have prepared an editable Google sheet containing all generated data. You can access it through the following link: Google Sheet Link.

Can someone assist in rewriting the formula or suggest an alternative approach to achieve the desired result?

Answer 1

Formula

=QUERY(Data!A2:D, 
   "Select A, B, 1-SUM(C)/SUM(D) Where A is not NULL
    group by A, B label 1-SUM(C)/SUM(D) ''")

Alternate Formula

Same as above but adds the $ columns as well.

=QUERY(Data!A2:E, 
  "Select A, B, SUM(C), SUM(E), SUM(D), 1-SUM(C)/SUM(D)
   where A is not NULL group by A, B label SUM(C) '', 
   SUM(E) '', SUM(D) '', 1-SUM(C)/SUM(D) ''")