Finding first Number in column not = "" 1

[kwbMitel]

Excel - 2007

I have a repetitive task I'm trying to automate

I have a formula in a column that either generates a number or "" depending on the results of an If statement.

The column is 50000 rows. I want to find and display the value in the lowest row number that is not "".

So far I've just been using the filter but that is getting old fast. Must be a better way, formula to help.

e.g.[tt]
A B C
1 9
2 8
3 7
4 6
5 5 5
6 4 6
7 3 7
8 2 8
9 1 9[/tt]

C contains a formula saying If value in column A is >= Column B then value = Column A otherwise blank

I want a formula to locate first number in column C (in example digit 5 would be returned)




**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

hi,
[tt]
=INDEX(C:C,COUNTA(C:C)-COUNTIF(C:C,">0")+1,1)[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

Thanks Skip,

The formula is returning a zero though.

with the example, if I start the range at the number I want to find it retruns a 5. Using the whole column returns a zero.

I'm researching Index but maybe you'll know what's wrong right away.


**********************************************
What's most important is that you realise ... There is no spoon.

 

[SkipVought]

I copied your example to A1 and parsed the data into 3 columns.

I substituted this formula in column C
[tt]
=IF(A1>4,A1,"")
[/tt]
in order to get the results in column C to be identical to yours.

My formula returns 5.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Be absolutely sure that you have a formula in each cell in column C, where there is adjacent data!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

I was just going there.

**********************************************
What's most important is that you realise ... There is no spoon.

 

[kwbMitel]

That did it. I simply had to change the range to properly check the array. With my example, I hadn't actually used a formula so there was no data in the first 4 cells.

Problem solved - you're a star! Even if a can't seem to be able to give you one.

**********************************************
What's most important is that you realise ... There is no spoon.