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?

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 nonerror value in a range:
This will find the first 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. 

