almostgotit
New Member
- Joined
- Nov 1, 2007
- Messages
- 8
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?
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?