Using IFERROR and AVERAGE - need help

[casthinker]

I have a column I want to average. It has the same formula in each cell and that formula turns in to a number when other rows in that column are filled out. Some of the rows do not have all numbers in it yet so it will show as #NUM!. If I try and average the column with the errors in the cells it does not work.

I have attached a file to show what I mean. I was told you could use AVERAGE and the IFERROR in one statement using the range of cells, but I cannot get it to work. Can someone just show me the formula so I can keep the #NUM! value but have the column averaged correctly? Thanks for the help!!!

 

[Sara]

Hi there

You could change the formula in F13 to this =AVERAGEIF(F3:F11,">"&0,F3:F11)

Combining the Average and IFError would only handle the error on the result of the average.
Using AverageIF and setting the criteria to greater than 0, it will only pick up numbers.

Or change the formula in F3:F11 to this =IFERROR(DATEDIF(B3,C3,"d"),"")
This manages the errors as they occur.

Regards
Sara

 

[Haseeb A]

In F3, use

=IF(COUNT(C3,B3)=2,C3-B3,"")

Then in F13, simply use AVERAGE()

 

[casthinker]

Thanks to both of you. When I saw the first post I used =AVERAGEIF(F3:F11,">"&0,F3:F11) and it worked perfectly!