Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 '12 at 13:14

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


share|improve this answer

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


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.


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.

share|improve this answer

You must log in to answer this question.

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