Index with blank cells

[Ged Salter]

Hi All

I have the following formula to find the last number in the cells A70 to A74. Ideally all cells have numbers in it so finding the last number works. Sometime, though, there are situations where one or more cells may have either a Zero or blank. This is where the formula falls down. How can I modify this to ignore any cells with either a zero or blank?



=IF(INDEX(A70:A74,COUNT(A70:A74))="",0,INDEX(A70:A74,COUNT(A70:A74)))

I should add that the numbers are not in numerical sequence. They are a random set of numbers. I just need to find the last number which is not a zero and also ignore the zeros and blank cells

Thanks in advance

regards

Ged

[Pepe Le Mokko]

Like

Please Login or Register  to view this content.

adapt the range to tour needs

[Ged Salter]

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

I dont understand the 9.9999999999999999999E+307 reference?

[JeteMc]

The primary concern is if the formula provided yields the results you need.
Once that is established then we may be able to help in understanding how it works.