Finding the date in a table when one value first exceeds another.

R

Rich P

I have a table with three columns which show a date, balance owed and savings
respectively. I’m 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(Cn>Bn,An,"") then
in my cell put the formula =min(D1:D100). This works but is there a neater
way without having the fourth column?
 
M

Max

Assuming data within A2:C7
Try, array-entered (press CTRL+SHIFT+ENTER) in say, D2:
=MIN(IF(C2:C7>B2:B7,A2:A7))
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Project a date and calculate a % in one formula 4
How to check for a date range match in one column and then count thevalues equal to in another 5
When building a chart Dates are been included that are not in thedate range 1
look up using 2 cells from one sheet to find a value in another... 7
Selecting value from second list when date is within date range on first list 5
Search for a value in column A and return the vaule/values in column B to column C 3
Lookup the month in a date string 01/03/05 1
Comparing Date Ranges in one Table to Date Ranges in another Table 3

Top