min, match, offset problem

[seanh1016]

I have two columns, column A=months, column B=values. Column B has some negative values in it. How do I find the closest value to zero in column B, then figure out which month this value occurs?

I don't want the lowest number -- just the one closest to zero. Would the min function still work here?

Thanks

 

[seanh1016]

Yes. Sorry I wasn't more specific.

 

[Aladin Akyurek]

Yes...

Book12
	A	B	C	D	E
18	Month	BegBal			Closestto
19	0	0			0
20	1	1808.62			91.13
21	2	1670.66			13
22	3	1531.78
23	4	1391.98
24	5	1251.25
25	6	1109.58
26	7	966.97
27	8	823.41
28	9	678.89
29	10	533.41
30	11	386.96
31	12	239.54
32	13	91.13
33	14	-58.26
34	15	-208.65
35	16	-360.04
36	17	-512.44
37	18	-665.85
38	19	-820.28
39	20	-975.74
Sheet1

Formulas...

E20:

=INDEX(B20:B39,MATCH(MIN(IF(B20:B39-E19>=0,B20:B39)),IF(B20:B39-E19>=0,B20:B39),0))

which must be confirmed with control+shift+enter instead of just with enter.

E21:

=INDEX(A20:A39,MATCH(E20,B20:B39,0))

 

[rrdonutz]

Another approach is to use one of the built-in financial functions, NPER(). Given your data, it appears that:

=NPER(D12,-D16,E19)

will return the number you are after, since the form of the function is

=NPER(Rate,Pmt,Pv,Fv,Type)

and you know the 3 arguments necessary to solve for the number of periods. Namely, you know the interest rate, the payment, and the present value (initial principal balance), which are constant in your example. Note that the last 2 arguments for this function are optional. That is, if they are omitted, they are assumed to be 0.

Finally, the CEILING function will round up the answer provided by NPER, since it doesn't always return a whole number. So, the final form would be:

=CEILING(NPER(D12,-D16,E19),1)

--Tom

 

[seanh1016]

They both work, thanks.

Tom, how did you know what cell my rate, pv, and pmt were in?

 

[seanh1016]

Aladin, yours works great. I combined both formulas to make one formula so I didn't have to do two steps. I came up with:

=INDEX(A20:A79,MATCH(INDEX(B20:B39,MATCH(MIN(IF(B20:B39-A19>=0,B20:B39)),IF(B20:B39-A19>=0,B20:B39),0)),B20:B79,0))

works great.

Tom, yours is good. But doesn't work in this case b/c it only factors in the rate, pmt, and pv -- and doesn't factor in if I were to pay more in a random month(which should decrease the payoff months). Aladin's solution simply looks at the months and the beginning balance.