Good evening All,
I'm not terribly surprised in QBO's deficiency -- the lack of this report is just another example, but here is my solution:
Three parts are shown: 1) the basic QBO Invoices and payments report (Col A-G), 2) the "Average" calculations (Col I-L), and 3) the formulas used in Col O.
A couple of "helper columns are used to "realign" the payment with the corresponding invoices. The formulas listed in col L, are each copied to the respective columns, then copy & pasted to the bottom of the report -- in my case row 34.
The first problem is in separating and identifying which lines are payments versus invoices. The col I & J formulas check and transfer only the Payment number and date, but for each line/invoice to which they apply. Col K formula returns the number of days between the invoice date (Col B and payment date Col J), but only for invoices -- payments always return -1. Finally (almost), Col L take the average of all payments with the same number (Col I), but does not include the payment (K= -1) in the average -- this happens only when the payment numbers change [caused by the outer if()]. Finally, (for real now) the bottom of Col L a standard "Average()" is used to obtain the average days to pay. Not to be an over achiever (LOL) but since I was here I added Min()/Max() but only on "Invoices", too.
Part of the beauty of this report, is that it can be easily used as a template by running the base report, then copying the report to the left side of the template, there will be some minor clean-up needed on the right side (based on number of rows), but the formulas should only need to be copied/moved down.
Hopefully, this will make the formulas easier to work will
And the bottom:
Hope that helps.