In article <(E-Mail Removed)>, Ron

Rosenfeld <(E-Mail Removed)> says...

>

> On Sun, 5 Aug 2012 12:51:38 -0400, Stan Brown <(E-Mail Removed)> wrote:

> > This is a spreadsheet of loan payments, interest, etc. Payment

> > dates are in B18:B377, and new balances are in I18:I377. I'm

> > trying to find the date where new balance first goes to zero. I

> > can't use LOOKUP or LOOKUP because the balances are in descending

> > order, so I tried MATCH:

> >

> >=MATCH(0,I$18:I$377,0)

> >

> >and then I'll use INDEX(B18:B377, the MATCH) to get the date. But

> >=MATCH() returns #N/A and not the row number as expected.

>

> MATCH is returning #N/A because there is no cell in that range that

> is equal to zero. You may have cells that display a zero, but that

> will be due to rounding of the actual value.
I thought of that, but I thought I was safe because the values in the

cells are the result of prior computations that are wrapped in =ROUND

(...,2). There must be something else going on that I overlooked,

that makes the value not precisely zero.

[pause to think and experiment]

Duh -- of course! I had forgotten that even with =ROUND(...,2), if I

add and subtract such rounded numbers the result may not be precisely

zero.

The first zero value, visually, is I239. However, =I239>0 returns

TRUE. When I display I239 in scientific notation I get 6.5E-11.

> There are several possible workarounds. I would recommend the

> following to get the pay-off date:

>

> =LOOKUP(2,1/(I18:I377>0),B19:B378)
Unfortunately this returns 0. (You're right that all values in I are

zero after the last payment.) I'm not sure what you're trying to do

here. Could it have the same problem?

And sure enough! When I change >0 to >=0.005, the formula works. It

also works when I leave the >0 but change the computation of new

balance to wrap inside =ROUND(...,2).

Lesson to myself here: because of the way floating point works, it

may actually be necessary to use "redundant" rounding in other

workbooks.

Thanks so much for your prompt help, Ron. I've posted the revised

workbook at

http://oakroadsystems.com/math/loan.htm#Complicated
and of course have credited you for help with the formula.

P.S. I tried emailing you, but it looks like "nospan.net" is a fake

domain. Could you add .invalid to the end of it, please, so that

humans know not to try it in email?

--

Stan Brown, Oak Road Systems, Tompkins County, New York, USA

http://OakRoadSystems.com
Shikata ga nai...

--

Stan Brown, Oak Road Systems, Tompkins County, New York, USA

http://OakRoadSystems.com
Shikata ga nai...