1

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.

0

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).

  • That's great - many thanks. The first part is perfect exactly what I am after. In the second part is there a way of doing this without the helper columns? It will eventually be quite a complex sheet and the helper columns double the size – Kenton Ward May 3 '18 at 13:41
  • @KentonWard Yes, it's possible, but much more complex and I often have to look it up myself. Will update my answer in a bit then. – Jerry May 3 '18 at 13:48
  • @KentonWard Updated. – Jerry May 3 '18 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.