Trying to show in one cell the date at which a cash flow will become negative

Question

I made a cash flow projections sheet with columns going from D to FE and Rows going from 1 to 65.

On the top of each column I have a row with dates from D8 to FE8. On the bottom I have a row with the "cash on hand" resulting values going from D65 to FE65...

I'm trying to create two cells in which

1) In one cell I would show the DATE (pulled from that row #8) when, given the data pulled from the cells in row #65, when the client would enter a cash flow negative it would show up in that cell I created...

2) another cell that would show when the value of "cash on hand" would fall below a certain amount - in my case $5,000. And I have that "minimum alert" amount in a cell right above where I want it... so if anyone wants to see the date when the "cash on hand" would fall below another amount, they could just enter it in that cell instead of trying to adjust the formula.

I'm new to this forum, so if I'm not making sense, please let me know.

Thanks

Answer 1

For when the amount falls below the Alert amount, you can use this formula which must be array-entered:

=INDEX(D8:FE8,1,MATCH(TRUE,D65:FE65<AlertAmount,0))

---

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl+shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

And for when the cash flow becomes negative, merely change AlertAmount to zero.