Value of 1 column before the last value in a contiguous range

[hsandeep]

I am having K15:W15 which generates either a numerical number which is non zero or non blank; 0; blank.

I want an excel formula to get non zero and non blank value lying in the cell which should be 1 column before the last non zero non empty value of K15:W15.

 

[Kaper]

One of possible answers could be:

=INDEX(15:15,1,MAX(IF((K15:W15=0)+(K15:W15=""),"",COLUMN(K15:W15)))-1)

and two comments:
* Are you still using Excel 2010 as mentioned in your profile?
If not, please adjust your profile in https://www.mrexcel.com/board/account/
* It could be that in older versions of Excel you shall commit this formula as an array one. So if you are not getting expected result set as active cell the cell with the formula. Press F2 to get into editing of formula and commit it not just with Enter, bur with Ctrl+Shift+Enter

 

[hsandeep]

One of possible answers could be:

=INDEX(15:15,1,MAX(IF((K15:W15=0)+(K15:W15=""),"",COLUMN(K15:W15)))-1)

and two comments:
* Are you still using Excel 2010 as mentioned in your profile?
If not, please adjust your profile in https://www.mrexcel.com/board/account/
* It could be that in older versions of Excel you shall commit this formula as an array one. So if you are not getting expected result set as active cell the cell with the formula. Press F2 to get into editing of formula and commit it not just with Enter, bur with Ctrl+Shift+Enter

I think The better solution would be to use

=IFERROR(INDEX(K15:W15, SMALL(IF((K15:W15<>0), COLUMN(K15:W15)-COLUMN(K15)+1), COUNTIF(K15:W15,"<>0")-1)), 0)

Comments please.

 

[Kaper]

Why shall it be better? Have you tested both?
And again: what is your Excel version (may be in Ex 2010 calculations on large ranges were not that well optimized, so a minor sub-version could be a bit quicker, especially if you had to use array commitment):

=INDEX(A15:W15,1,MAX(IF((K15:W15=0)+(K15:W15=""),"",COLUMN(K15:W15)))-1)

Have a look also on yet another modification:

=INDEX(A15:W15,1,MAX(IF(K15:W15<>0,COLUMN(K15:W15),""))-1)