Add numbers backwards cumulatively until certain threshold is found

Interesting…what version of Excel are you using? If it’s Excel 365, a formula using recursion could be used, otherwise you’d need helper cells. Alternatively, VBA could be used. You can update your account details to include the Excel version.

KRice said:

Interesting…what version of Excel are you using? If it’s Excel 365, a formula using recursion could be used, otherwise you’d need helper cells. Alternatively, VBA could be used. You can update your account details to include the Excel version.

Click to expand...

Hii KRice! I have access to Excel 365, yes. I never heard about recursion - let me look into that.

I'm not familiar with Days Sales Outstanding (DSO), so I needed to read a little more about it. While it appears that there are several different methods for computing it, I believe you're describing the Countback method. As a metric for estimating the number of days of average sales that are currently outstanding, for any given month, we need to look at that month's gross sales and accounts receivable, and then possibly look back sequentially at the preceding month's gross sales until the sum of gross sales (from the current month until some earlier month) offset the current month's account receivables balance. For whichever month the offset is encountered, the average daily sales that month establishes the number of days from that month that should be used, as well as the number of days for any intervening months.

Here is one approach that I believe will work for your purposes. I'm not sure how far back in time the records might go, so I've used dynamic ranges that assume we can determine the bottom of the data block by finding the last non-blank cell in the Period column (column A)...and the last row number is assigned to the "lrow" variable. The "periods" are assumed to actually be dates--the first days of the month/year associated with the financial numbers, or actually any date in the month-year to be displayed, and then the cell is formatted to show the date in the format "mmm-yy". This array of dates (periods..."pd") begins in A2 and extends down to row "lrow". The advantage in using a date in this column is that a formula can be used to return the number of days in that month, so a days columns isn't necessary. Similarly, the gross sales "gs" begin in cell B2 and extend down to row "lrow". The current month's accounts receivable balance is variable "ar", assumed to be in cell C2. Then we establish the array of the number of days in each month ("ndm") shown in each period "pd". Note that all of these arrays do not have a fixed cell reference for the beginning of their ranges, but they all terminate at row "lrow". So as the main formula is copied down the table, the DSO for each month will be based on financial data that begins in that month and potentially extends back in time to the bottom of the table.

Then the more complicated part: we form an array of the cumulative sum of gross sales "gs" for each period and subtract from those sums the "ar" amount to create the difference "d" array...the difference between the cumulative sums of "gs" and "ar". Based on the Countback methodology, we know that when this "d" array first becomes >=0, then we have gone back far enough (in time) such that the sum of gross sales back to that earlier period will be sufficient to offset the current "ar" balance. We find the array index of this earlier period and assign it to variable "r". Then we compute the number of days required from each period in the "e" array, and then in the "f" array, we limit the maximum number of days to be taken from the "e" array by considering the maximum number of days that can be contributed by that period based on "ndm". Finally, the sum of the first "r" results in the "f" array is taken. In some cases an error will be the result if the financial data do not allow for the "ar" balance to be offset (MATCH never finds the "r" value"), so an error trap returns a message.

Same formula applied to your original data: