cancel
Showing results for 
Search instead for 
Did you mean: 

Reply to message

View discussion in a popup

Replying to:
Level 3

Reply to message

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.

 

Report Average days to Pay.JPG

 

Hopefully, this will make the formulas easier to work will

I (eye)IF(C7="Payment",E7,I5)
JIF(I8=I7,J7,G8) 
KIF(C7="Invoice",J6-G7,-1)
LIF(I7=I6,"",AVERAGEIFS(K:K,I:I,I7,K:K, ">=0"))

 

And the bottom:

LAVERAGE(L7:L34)
LMINIFS(F:F,C:C,"Invoice")
LMAXIFS(F:F,C:C,"Invoice")

 

Hope that helps.

 

 

Need to get in touch?

Contact us