EXCEL 1st # in list where values stay above 1

October 22, 2010 at 11:12:37
Specs: Windows XP pro SP3
Hi
I would like to use a function to get the first data point from the 2nd column of a table

When values in the 1st column have increased above 1 and stayed above 1 in all subsequent cells.

ie the number 8 would be returned from a vlookup function =vlookup(?>1?,A1:B8,2) in the data set below.

0.9 3
1.0 4
1.1 5
0.9 6
0.9 7
1.0 8
1.1 9
1.2 10


See More: EXCEL 1st # in list where values stay above 1

Report •


#1
October 22, 2010 at 20:30:07
re: I would like to use a function...

Go right ahead.

If you need any help, feel free to ask.


Report •

#2
October 25, 2010 at 17:11:10
Thats brilliant, I will ask then, thanks

I would like to know which function to use in excel to get the first data point from the 2nd column of a table, when the values in the 1st column have increased above 1 and stayed above 1 in all subsequent cells in the list.

i.e. what do I put between the ? ? in this V lookup formula or is there another way?

=vlookup(?>1?,A1:B8,2)

In the data set below, the resultant value would be 8

0.9 , 3
1.0 , 4
1.1 , 5
0.9 , 6
0.9 , 7
1.0 , 8
1.1 , 9
1.2 , 10

Thanks in advance for any helpful responses


Report •

#3
October 26, 2010 at 06:34:13
There is nothing you can put in a VLOOKUP function that will get you the results you want.

There is no built-in Excel function that will get you the results you want.

You will need to use a User Defined Function (UDF)

1 - Open the VBA editor.
2 - Click Insert...Module
3 - Paste the following code into the pane that opens.
4 - With your table in starting in A1, enter =FirstOne() in any cell.

Function FirstOne()
Application.Volatile
'Find last row in Column A
 lstRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through range
  For rw = 1 To lstRow
'Is Value >= 1?
   If Range("A" & rw) >= 1 Then
'If it is, are all values below it >=1?
    If Not Application.WorksheetFunction.CountIf _
           (Range("A" & rw + 1 & ":A" & lstRow), "<1") > 0 Then
'If yes, then we're done
     Exit For
    End If
   End If
  Next
'Set Function value to corresponding cell in Column B
  FirstOne = Range("B" & rw)
End Function



Report •

Related Solutions

#4
October 27, 2010 at 10:08:20
Great thanks

Report •


Ask Question