Array Question: Return the first date when Row 2 exceeds Row 1

sasUrbanexcel

New Member
Joined
Jun 14, 2012
Messages
2
Hello,
I'm trying to return the first date when the numbers in Row 2 exceed Row 3. I can see the first date is 2/1/15 (when 236,151>215,653), but i dont know how to write the formula.

Row 109/01/1410/01/1411/01/1412/01/1401/01/1502/01/1503/01/1504/01/1505/01/1506/01/1507/01/1508/01/15
Row 2 - - 9,962 75,058 140,210 236,151 302,123 303,614 303,661 304,876 306,308 307,814
Row 3 210,644 212,614 214,284 215,653 216,565 217,164 217,761 218,364 218,966 219,568

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>


Thank you
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hello,
I'm trying to return the first date when the numbers in Row 2 exceed Row 3. I can see the first date is 2/1/15 (when 236,151>215,653), but i dont know how to write the formula.

Row 1
09/01/14
10/01/14
11/01/14
12/01/14
01/01/15
02/01/15
03/01/15
04/01/15
05/01/15
06/01/15
07/01/15
08/01/15
Row 2
-
-
9,962
75,058
140,210
236,151
302,123
303,614
303,661
304,876
306,308
307,814
Row 3
210,644
212,614
214,284
215,653
216,565
217,164
217,761
218,364
218,966
219,568

<TBODY>
</TBODY>


Thank you

You could do that with conditional formatting, but here is a macro snippet that will also do it.

Code:
Sub par()
Dim sh As Worksheet, rng As Range, lc As Long
Set sh = Sheets(1) 'Edit sheet name
lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Range(sh.Cells(2, 1), sh.Cells(2, lc))
For Each c In rng
If c > c.Offset(1, 0) Then
myCell = c.Offset(-1, 0)
MsgBox myCell
Exit For
End If
Next
End Sub
Code:
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hello,
I'm trying to return the first date when the numbers in Row 2 exceed Row 3. I can see the first date is 2/1/15 (when 236,151>215,653), but i dont know how to write the formula.

Row 1
09/01/14
10/01/14
11/01/14
12/01/14
01/01/15
02/01/15
03/01/15
04/01/15
05/01/15
06/01/15
07/01/15
08/01/15
Row 2
-
-
9,962
75,058
140,210
236,151
302,123
303,614
303,661
304,876
306,308
307,814
Row 3
210,644
212,614
214,284
215,653
216,565
217,164
217,761
218,364
218,966
219,568

<TBODY>
</TBODY>


Thank you
Try this array formula**:

=INDEX(B1:F1,MATCH(TRUE,B2:F2>B3:F3,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format the result as Date
 

sasUrbanexcel

New Member
Joined
Jun 14, 2012
Messages
2
This is perfect. Thank you!

Try this array formula**:

=INDEX(B1:F1,MATCH(TRUE,B2:F2>B3:F3,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format the result as Date
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,492
Members
417,028
Latest member
JFCLUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top