How to find a value that constantly changes in a column

[Chanko]

Hi,

I want to create a function that finds the first value that does not equal to “NA” in column C and then return that value in a cell I designate i.e. cell F2. Please see attachment.

In this example, it's a stock dividend estimate but this changes overtime. Ideally, I want to see the value "0.57" in cell F2.

Thanks.

Best regards,
Jeff

[benishiryo]

hi Jeff, welcome to the forum. try this array formula:
=INDEX(C2:C264,MIN(IF(ISNUMBER(C2:C264),ROW(C2:C264)))-ROW(C2)+1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will might an error or a clearly incorrect answer. Press F2 on that cell and try again.

Edit: an addition if there are no numbers:
=IF(COUNT($C$2:$C$264),INDEX(C2:C264,MIN(IF(ISNUMBER(C2:C264),ROW(C2:C264)))-ROW(C2)+1),"No numbers")

[humdingaling]

Ctrl - Shift - Enter
=INDEX(C3:C264,MIN(IF(ISNUMBER(C3:C264),ROW(C3:C264))))

[humdingaling]

Beni what is the row(C2)+1 do?

[benishiryo]

@humdingaling:
your INDEX formula for the array portion starts from C3:C264. so C3 is considered row 1 of your array, C4 row 2, & so on. by using ROW(C3:C264), you are listing numbers from 3 to 264:
{3;4;5;6;...262;263;264}
it should start from 1 to 262. so i did a -ROW(C2). that is to minus the starting row so it becomes 0 to 261. then +1 to make it 1 to 262. your value is now seemingly "correct" because C88:C90 has the same values. change C90 to a different value & you won't get 0.57 anymore.

[wenqq3]

How about this?

Please Login or Register  to view this content.

with CTRL+SHIFT+ENTER

[humdingaling]

Thanks for clarifying, so if my array is C1:C264 then i would not need row bit at the end?

[benishiryo]

so if my array is C1:C264 then i would not need row bit at the end?

yes. there are a few variations people do. another variation is:
=INDEX(C:C,MIN(IF(ISNUMBER(C3:C264),ROW(C3:C264))))
this one above & the one i used are my preferred choices. OP often provides sample files & need to change some cell references to apply back to the original file. the 2 choices i mentioned allows them to insert or delete above C3 & still work. that's just a personal preference of course.

[Chanko]

Hi Guys,

Thanks for getting back to me.

Let me check this out.

[Chanko]

Hi Beni,

Makes perfect sense - brilliant!

Thanks for the quick response.