Return week # from last input in a column

[Turkish Delight]

Hi Guys,

I have 2 columns for "Wk" [column A] and "cost" [Column B]. The cost column is all 0s[from formulas] until a number is inserted.
I would like a formula please under yellow cell F2 to spit out the week number where there is the latest value in cost column B that does not =0.

Eg below F6 formula would be Week 6 as the last value in column B is 41 [B7] as everything after it is 0:

In this example F2 formula would be Week 10 as the last value is 74 [cell B11] with all other cells after in this row are 0:

Would you know what formula I can put for F2 please?

Many thanks!

 

[hagia_sofia]

Hello, try:

=TAKE(FILTER(B2:B21,B2:B21),-1)

 

[Fluff]

How about

=LOOKUP(2,1/(B2:B37<>0),A2:A37)

 

[Turkish Delight]

Amazing many thanks both!

 

[Turkish Delight]

How about

=LOOKUP(2,1/(B2:B37<>0),A2:A37)

Hi Fluff,

Would you be able to explain this formula please for my understanding? Not sure what is trying to do..

Many thanks,
Vid

 

[Fluff]

Have a look here Get value of last non-empty cell
Scroll down about half way to "Older versions of Excel"

 

[Turkish Delight]

Have a look here Get value of last non-empty cell
Scroll down about half way to "Older versions of Excel"

Many thanks learned something new!

 

[Fluff]

Glad we could help & thanks for the feedback.