How to show which column has the first amount and count columns to reach a sum amount

Question

I am working on a Google sheet that shows a sum that has been advanced to a customer and then shows the income per month the customer receives. We will retain 100% of the income until the amount that has been advanced is repaid.

I am trying to come up with two formulas for this spreadsheet which will work on each row:

1. in column C that returns the first month that revenue is earned by the client and
2. in column D that returns the number of months over which the client revenues pay back the advance listed in column B.

My research suggests that this is probably possible using an array function but I can't seem to find an example and don't understand the syntax enough to make it work.

Answer 1

I would suggest using some helper columns for the second part.

This said, for the first part, I propose this formula:

=index($E$2:$P$2,match(false,isblank(E3:P3),0))

For the second part, I propose this formula (note it has dependents on the helper columns)

=datedif(C3,index($Q$2:$AB$2,match(true,Q3:X3>=B3,0)),"M")+1

The helper columns only have running totals (adds up everything up to but excluding the current month).

Datedif finds the difference between two dates, and in the above, will return that difference in months.

Spreadsheet sample

---

EDIT: Added the formula for running totals for the second part, which works without the helper columns:

=DATEDIF(C3,index($E$2:$P$2,match(true,MMULT((column(E3:P3)<=transpose(column(E3:P3)))*E3:P3,transpose(SIGN(E3:P3)))>=B3,0)),"M")+1

This bit:

MMULT((column(E3:P3)<=transpose(column(E3:P3)))*E3:P3,transpose(SIGN(E3:P3)))

Returns an array containing the running totals, referenced from this website and adapted to a row (the website uses a column of values and explains in fairly good detail how it works).