| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Find first positive value in row 3 and return date from row 1
Hi everyone, so here's the issue: I have a sheet that tracks cash flow in & out of a business unit. In row 1 I have a series of dates [e.g. 7/14/05, 7/21/05, 7/28/05 etc.] and in row 3 I have a series of values, starting out negative but at some point (depending on a couple of variables) turning positive [e.g. (1346.02), (574.16), 211.86]. What I need to do is in cell A5, return the date from row 1 that corresponds to the first positive value in row 3. In the above example it would return 7/28/05 Thank you in advance for any help you can provide. rlaw68 -- rlaw68 ------------------------------------------------------------------------ rlaw68's Profile: http://www.excelforum.com/member.php...fo&userid=5744 View this thread: http://www.excelforum.com/showthread...hreadid=389607 |
|
#2
|
|||
|
|||
|
=INDEX(1:1,,MIN(IF(3:30,COLUMN(3:3))))
This is an array formula so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "rlaw68" wrote in message ... Hi everyone, so here's the issue: I have a sheet that tracks cash flow in & out of a business unit. In row 1 I have a series of dates [e.g. 7/14/05, 7/21/05, 7/28/05 etc.] and in row 3 I have a series of values, starting out negative but at some point (depending on a couple of variables) turning positive [e.g. (1346.02), (574.16), 211.86]. What I need to do is in cell A5, return the date from row 1 that corresponds to the first positive value in row 3. In the above example it would return 7/28/05 Thank you in advance for any help you can provide. rlaw68 -- rlaw68 ------------------------------------------------------------------------ rlaw68's Profile: http://www.excelforum.com/member.php...fo&userid=5744 View this thread: http://www.excelforum.com/showthread...hreadid=389607 |
|
#3
|
|||
|
|||
|
Hi!
Dates in A1:C1 Values in A3:C3 Enter this formula in A5 using the key combo of CTRL,SHIFT,ENTER: =INDEX(A1:C1,MATCH(TRUE,A3:C30,0)) Biff "rlaw68" wrote in message ... Hi everyone, so here's the issue: I have a sheet that tracks cash flow in & out of a business unit. In row 1 I have a series of dates [e.g. 7/14/05, 7/21/05, 7/28/05 etc.] and in row 3 I have a series of values, starting out negative but at some point (depending on a couple of variables) turning positive [e.g. (1346.02), (574.16), 211.86]. What I need to do is in cell A5, return the date from row 1 that corresponds to the first positive value in row 3. In the above example it would return 7/28/05 Thank you in advance for any help you can provide. rlaw68 -- rlaw68 ------------------------------------------------------------------------ rlaw68's Profile: http://www.excelforum.com/member.php...fo&userid=5744 View this thread: http://www.excelforum.com/showthread...hreadid=389607 |
|
#4
|
|||
|
|||
|
Thanks to Bob & Biff for the suggestions. I ended up using a modified version of Bob's as my actual calculation/range was a bit more complex than my example. I appreciate the quick response. Sincerely, rlaw68 -- rlaw68 ------------------------------------------------------------------------ rlaw68's Profile: http://www.excelforum.com/member.php...fo&userid=5744 View this thread: http://www.excelforum.com/showthread...hreadid=389607 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
| Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
| Return a date 6 months from a date in another cell | Excel Worksheet Functions | |||
| Return the end of month date from a date | Excel Worksheet Functions | |||
Hybrid Mode
