Hi,

I have a workbook that imports data with PI datalink, however sometimes we get** I/O timeout** or **#value ** this effects all the formulas we have in the sheet such as average

Would it be possible to use **averageif** function if we have I/O timeout or #value errors for the following code?

Range("I7").Value = "=average(G4:G" & lastrow & ")"

I tired to add it to average values above zero however it didnt work

Range("I7").Value = "=averageif(G4:G" & lastrow & ",>0)"

Hi Saif,

Juan's suggestion to use KB00929 is a good general suggestion. To answer your specific question concerning AVERAGEIF, for a sample range A1:A5, try the following:

=AVERAGEIF($A$1:$A$5,"<=9.99E+307")

9.99e307 is the largest number Excel can store, so effectively you're telling Excel that if the cell contains a number, include it; otherwise don't. As you can see from the screenshot, this works for errors (#VALUE), text (I/O Timeout), and blank cells.