Return 2nd Last Value in Dynamic Range

[rob51852]

Hello


Column DY in sheet Data W1 is populated by a number in the following row each minute by a third party program. I would like a formula to return the second last value in the column and thought the following would work but it actually returns the 3rd last value. Can anyone see where I'm going wrong?

=IF(A6<>"",INDEX('Data W1'!$DZ:$DZ,COUNTA('Data W1'!$DZ:$DZ)-0),"")

Thank you

 

[DanteAmor]

Try

=INDEX('Data W1'!DZ:DZ,COUNTA('Data W1'!DZ:DZ)-1)

Note:
also check if you have blank cells in the range where you have data

 

[rob51852]

Doh! It’s the blank cells. Thanks

 

[DanteAmor]

Doh! It’s the blank cells. Thanks

I'm glad to help you. Thanks for the feedback.