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 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.