PC Review


Reply
Thread Tools Rate Thread

First row where a cell is zero

 
 
Stan Brown
Guest
Posts: n/a
 
      5th Aug 2012
I'm trying to do a lookup, but on data that occur in _descending_
order, and for some reason I can't get it to work.

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
fond the date where new balance first goes to zero. I can't use
VLOOKUP 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. Is the
problem that my column I contains formulas rather than values? Excel
help isn't explicit about this, though all its examples are values.

How can I find the first cell in I18:I377 that contains a zero? (I'm
using Excel 2010, but if possible I'd like a formula that also works
in Excel 2007.)

Thanks!

(P.S. This is in aid of my spreadsheet mentioned in "Here is Mortgage
Calculator with Amortization" -- I'm trying to generalize it, and
also display up top the date on which the loan will be paid off.)


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...
 
Reply With Quote
 
 
 
 
Stan Brown
Guest
Posts: n/a
 
      6th Aug 2012
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...
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
lock first row and first column WITHOUT selecting Cell "B2" first? Helmut Meukel Microsoft Excel Programming 3 28th Apr 2010 07:50 AM
if first cell assinged, then first cell rotate to last cell? how? Chaslefire Microsoft Excel Misc 0 31st Jan 2009 01:33 AM
First populated cell in row array/ Last populated cell in row arra =?Utf-8?B?U2t5c2Nhbg==?= Microsoft Excel Worksheet Functions 7 29th May 2008 05:20 PM
Make cell value zero if cell formula returns value less than zero? Fred Ernst Microsoft Excel Misc 2 20th Feb 2007 05:57 PM
if less then zero put zero if greater then zero state number =?Utf-8?B?UGFtZWxhIEplYW4=?= Microsoft Excel Worksheet Functions 2 17th Oct 2006 08:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:03 PM.