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