How to select the farthest number down a column which is a number

[Legacy 116183]

I have a row of numbers which work on a Year to Date Basis.

As we go through the year more and more of the cells become populated. I want to select the YTD figure and display this in a bottom row.

The difficulty is that those cells that haven't yet been used have a zero in them. How can I get this to select the last figure in the column excluding the zeros.

=OFFSET(Z5,0,COUNTA(Z5:Z57)) brings up week 52 which is zero of course.
=IF(R18=0,"",(SUM($R$5:R18)-SUM($J$5:J18))) This is the formula which is in ech cell from r5 to r52.

Thanks for any help.

 

[Rick Rothstein]

Try this formula (change the C5:C57 range reference to the range with your numbers and zeros)...

=MAX(ROW(C5:C57)*(C5:C57<>0))

NOTE
----------------
If you are working in XL2019 or earlier, you will have to array-enter the formula using CTRL+SHIFT+ENTER and not Enter by itself.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Legacy 116183]

Thanks, perhaps I'm misunderstanding what you are trying say here as it brought the answer of 57.

=MAX(ROW(Z5:Z57)*(Z5:Z57<>0)) I did note that you had Z7 in the second section of the formula.

 

[Legacy 116183]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. I have done that. It's Office 2013

 

[Peter_SSs]

Thanks for updating version.

Does this do what you want?

=LOOKUP(9.99E+307,Z5:Z57/(Z5:Z57<>0))

 

[Legacy 116183]

=LOOKUP(9.99E+307,Z5:Z57/(Z5:Z57<>0))

It does, what does the 9.99E + 307 do?

 

[Peter_SSs]

It does

Good news!

what does the 9.99E + 307 do?

It makes a very big number ( 9.99 multiplied by 10 to the power of 307 ) roughly the largest number allowed in Excel (scroll down here)
When the LOOKUP cannot find that exact number it returns the last number in the list produced by the divisions.

 

[Legacy 116183]

Good news!


It makes a very big number ( 9.99 multiplied by 10 to the power of 307 ) roughly the largest number allowed in Excel (scroll down here)
When the LOOKUP cannot find that exact number it returns the last number in the list produced by the divisions.

Thank you very much.

 

[Peter_SSs]

You're welcome.