First row where a cell is zero

Discussion in 'Microsoft Excel Worksheet Functions' started by Stan Brown, Aug 5, 2012.

  1. Stan Brown

    Stan Brown Guest

    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...
     
    Stan Brown, Aug 5, 2012
    #1
    1. Advertisements

  2. Stan Brown

    Stan Brown Guest

    In article <>, Ron
    Rosenfeld <> says...
    >
    > On Sun, 5 Aug 2012 12:51:38 -0400, Stan Brown <> 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...
     
    Stan Brown, Aug 6, 2012
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Convert cell from last name first to first name first

    Guest, Oct 21, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    353
    JE McGimpsey
    Oct 21, 2004
  2. First row in Selection range (first index of a cell) EXCEL VBA

    , Mar 20, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    1,221
    Guest
    Mar 20, 2006
  3. Guest

    if less then zero put zero if greater then zero state number

    Guest, Oct 17, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    379
    Guest
    Oct 17, 2006
  4. Guest

    First populated cell in row array/ Last populated cell in row arra

    Guest, Jun 13, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    7
    Views:
    387
    Support Request
    May 29, 2008
  5. INTP56

    How to find the first row not equal to the value in the first row?

    INTP56, May 27, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    8
    Views:
    593
    RagDyeR
    May 28, 2008
Loading...

Share This Page