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

Cell Formulas | ||
---|---|---|

Range | Formula | |

D7 | D7 | =C7/D4 |

D8 | D8 | =C8/D4 |

D12 | D12 | =AVERAGEIF(D7:D11,"<>0") |

D13 | D13 | =STDEV.S(IF(D7:D11>0,D7:D11)) |

Press CTRL+SHIFT+ENTER to enter array formulas. |

The average for this range should be 1 if cells D8 to D11 had been excluded.

If formatting cell D8 as Text does not exclude the value in this cell, how can i adjust the AVERAGEIF formula to achieve this? However I still want to see the 'value' in cell D8 but dont want it as part of the calculation.

Thanks