# Return a value when a list becomes positive

#### almostgotit

##### New Member
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 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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Scott Huish

##### MrExcel MVP
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
ABCD
1Month 1:-170Month 5:
2Month 2:-220
3Month 3:-50
4Month 4:-25
5Month 5:5
6Month 6:5
Sheet1

Last edited:

#### almostgotit

##### New Member
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

##### MrExcel MVP
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.

Replies
2
Views
116
Replies
9
Views
316
Replies
3
Views
237
Replies
2
Views
396
Replies
0
Views
2K

1,191,612
Messages
5,987,672
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back