Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First row where a cell is zero
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First row where a cell is zero
On Sun, 5 Aug 2012 12:51:38 -0400, Stan Brown wrote:
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.) 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. There are several possible workarounds. I would recommend the following to get the pay-off date: =LOOKUP(2,1/(I18:I3770),B19:B378) The LOOKUP will match in column I the last number that is greater than zero. I am assuming that once there is a zero (or less), there will be no other values further down the column that are greater than zero, since you are writing about a Mortgage calculator. Note that the result vector is purposely offset by one row compared with the lookup vector; because the date you want should be one below the last positive value. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First row where a cell is zero
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, =I2390 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:I3770),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 he 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Programming | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |