SOLVED

# Finding crossing point for positive and negative values

Copper Contributor

# Finding crossing point for positive and negative values

I'm trying to figure out a formula that can determine when a retirement account goes negative (i.e. what age I run out of money) based on a column of monthly values.

I have a sheet that shows a schedule of retirement account values, with a column for date (monthly), a column for contribution amount (which increases every 12 months), and various scenario columns to show performance with variables for "retirement salary" and "assumed Rate of Return". Eventually, a bunch of the columns cross from positive to negative (run out of money). I'm looking for a formula to put at the top of each column that tells me what month the column changed from positive to negative. I could sort this out with a 'sister' column to each scenario to check for this transition. I'm just looking for something more elegant. Security on my work computer is such that VBA isn't an acceptable solution. Thanks!

3 Replies
best response confirmed by xPR1MUSx (Copper Contributor)
Solution

# Re: Finding crossing point for positive and negative values

``=INDEX(\$A\$2:\$A\$13,SMALL(IF(B2:B13<=0,ROW(\$A\$1:\$A\$12)),1))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

# Re: Finding crossing point for positive and negative values

That is perfect! I've used Index, but not that clever. SMALL is new to me. Thanks so much!

# Re: Finding crossing point for positive and negative values

I suspect the month you want returned depends on whether the 'account' is a month opening or month closing amount.  Anyway, a single schedule would require

``= XLOOKUP(0, account, month, "OK", -1)``

or, using a Lambda function,

``````= FinalMonthλ(account)

where FinalMonthλ
= LAMBDA(account,
XLOOKUP(0, account, month, "OK", -1)
)``````

If you have multiple scenarios to analyse simultaneously this would become

``````Worksheet formula
= BYCOL(table, FinalMonthλ)``````

1 best response

Accepted Solutions
best response confirmed by xPR1MUSx (Copper Contributor)
Solution

# Re: Finding crossing point for positive and negative values

``=INDEX(\$A\$2:\$A\$13,SMALL(IF(B2:B13<=0,ROW(\$A\$1:\$A\$12)),1))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.