I have the following range where some cells are either blank or contain zero. However there is one cell that contains a number generated by a formula. However despite formatting that cell as 'text' , the AVERAGEIF function still sees it as a number.

For the range below, the cell containing the number 2 which is has been generated from another formula i.e. =A1/C1 is still seen by the AVERAGE OR AVERAGEIF formula even if i have formatted this cell as 'text'.


F1:1, F2:2,F3:0,F4:0, F5:0 = average

The formula i have previously used was AVERAGEIF(F1:F5,"<>0") which excluded the cells containing zero. The average for this range should be 1 if cells F2 to F5 had been excluded.


If formatting cell F2 as Text does not exclude the value in this cell, how can i adjust the AVERAGEIF formula to achieve this?

I would have inserted an image but for some reason this option doesnt appear to be working.

Thanks