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?

  • 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



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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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