0

Ok, for example if my range is B1:B5000. This is a time series. At certain point in time, the values all become errors that are #NUM! So is there any formula I can use to return the last value that is not an error going down the range? Or would it be easier if I got rid of all the errors by using :=IFERROR(B1,NA()) and basically making another error free range? In this case, what excel formula would I use?

Improve this question
1
  • I posted an answer but note that NA() generates an error value! To eliminate errors and replace with spaces it could be =IFERROR(B1,"")
    – barry houdini
    Sep 28, 2012 at 13:14

2 Answers 2

Reset to default
1

Whether you eliminate errors or not you can find the last number in B1:B5000 (times are numbers in excel) with this formula

=LOOKUP(9.9E+307,B1:B5000)

Improve this answer
1

The short answer is you can use the following formula to find the last non-error value in a range:

=MATCH(ERROR.TYPE(#NUM!),B1:B5000,-1)

This will find the first #NUM! error and give you the last row prior, based upon a range of B1:B5000. Assuming your last good value was in Row10, it will return a value of 10.

LastError

If you're looking to use this in a charting application, or need to use a dynamic range as items are added, it will be more complex.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .