Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.