Return a value when a list becomes positive

[almostgotit]

I am creating a payback analysis for a purchase that has variable cash flows. I have a cummulative total row that generally starts negative (cash outflow at purchase) and eventually becomes positive.

I need a formula that returns the month that the cummulative total becomes positive. Here is what i have so far, but it doesn't work.

List of cummulative numbers:
Month 1: -170k
Month 2: -220k (additional purchase)
Month 3: -50k (income from purchase)
Month 4: -25k
Month 5: 5k (Cash Flow Positive - Return Month 5)
Month 6-20: >5k

My formula is: MATCH(0,$A$1:$H$1,1). This returns 4. Is there no way to make it ruturn month 5 without setting up an Index(Match).

Excel help says that "If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on." I cannot sort the list, I want the formula to be automated.

Any ideas?

 

[Scott Huish]

Are those actual numbers in the cell formatted with k or is it text that includes the letter k?

Assuming numbers, what's wrong with INDEX MATCH?

Formula in D1:
=INDEX(A1:A6,MATCH(TRUE,B1:B6>0,0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Excel Workbook
	A	B	C	D
1	Month 1:	-170		Month 5:
2	Month 2:	-220
3	Month 3:	-50
4	Month 4:	-25
5	Month 5:	5
6	Month 6:	5
Sheet1

 

[almostgotit]

That worked, thank you very much! Is there any way you can briefly explain why it only works as a Ctrl+Shift+Enter formula?

 

[Scott Huish]

It's an array formula, see here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

In this array formula the B1:B6>0 part is going to create an array of TRUE/FALSE values, and we are going to MATCH to the first TRUE value in this array.