| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a value in a Table | Excel Worksheet Functions | |||
| Change cell color if date exceeds formula | Excel Discussion (Misc queries) | |||
| finding value from table | Excel Worksheet Functions | |||
| finding a date/time in a list that is closest to an existing date/ | Excel Discussion (Misc queries) | |||
| Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
Linear Mode
