cannot exclude a cell when using AVERAGEIF

There are instructions at the top of the page explaining how to attach your sample workbook.

Formatting as Text after initial population won't change the underlying value (just the presentation), recommitting the formula will enforce the underlying value becomes Text.

i.e. if you re-commit the formula in F2 then =AVERAGEIF(F1:F5,"<>0") should return 1

edit: sorry -- my bad, rather than format as text, use =< calc >&"" to force to string (leaving as General/Number)

Note: some other calcs like COUNTIF for ex. will still treat the literal text as a number depending on use case

=COUNTIF(F1:F5,2) will return 1 even though the criteria is number, and the value in the range is "2"

if you put a numeric clause in - the "2" will be ignored

=COUNTIFS(F1:F5,2,F1:F5,">-9.99E+307")

Originally Posted by surfing96

I didnt want to attach a sample workbook. It was an image i wanted to insert within the body of the text field

Images are of little value, which is why we ask for sample workbooks instead. Besides, NOBODY should have to retype your data to be able to test a solution for you.

LOL! Well, we'd love it to work, but actually it would lead to the same dialog as the instructions at the top, allowing various types of attachment. An image is only ever really of any use if the OP and their helpers are seeing different behaviour in the same workbook. The paper clip has been broken for years - we can't get the owner and his tech team to fix it (they don't seem to care), which is embarrassing for us.

However, you are missing the point a bit: generally speaking, a workbook is MUCH more helpful than an image, for the reasons I outlined above, so please use that option in future. And please don't take offence when you are asked to do something a bit differently to what you expect - it's for YOUR benefit in the long run.