|
Finding the date in a table when one value first exceeds another.
I have a table with three columns which show a date, balance owed and savings
respectively. Im trying to find a cell formula that returns the date of the first instance where the savings exceeds the balance, i.e. gives me the date I could pay the balance off. In this example it would be 01/05/08. 01/01/08 65000 62000 01/02/08 64500 62260 01/03/08 64000 62520 01/04/08 63500 62780 01/05/08 63000 63040 01/06/08 62500 63300 At present I have a formula in a fourth column saying =if(CnBn,An,"") then in my cell put the formula =min(D1:D100). This works but is there a neater way without having the fourth column? |
Finding the date in a table when one value first exceeds another.
Assuming data within A2:C7
Try, array-entered (press CTRL+SHIFT+ENTER) in say, D2: =MIN(IF(C2:C7B2:B7,A2:A7)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rich P" wrote: I have a table with three columns which show a date, balance owed and savings respectively. Im trying to find a cell formula that returns the date of the first instance where the savings exceeds the balance, i.e. gives me the date I could pay the balance off. In this example it would be 01/05/08. 01/01/08 65000 62000 01/02/08 64500 62260 01/03/08 64000 62520 01/04/08 63500 62780 01/05/08 63000 63040 01/06/08 62500 63300 At present I have a formula in a fourth column saying =if(CnBn,An,"") then in my cell put the formula =min(D1:D100). This works but is there a neater way without having the fourth column? |
| All times are GMT +1. The time now is 09:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com