Thread: Average? sub Average?

I have a column of values.

Example:
38
43
77
74
42
0
0
0
0
0

I can average them at the bottom of the column with a formula... but the value is incorrect because it's also averaging all the 0's. Can you tell me how to get a subtotal of the cells that only have values in them? (The zeros have to be there because they are linked to another worksheet function.) I believe somewhere I may have to use absolute referencing... but I'm not certain where. Would it be sub averaging? Or am I barking up the wrong tree?

Hi and welcome to the board.
Question answered lots of times on this forum ( the Search tool works very well)

This being said try =average(if(a1:a10>0,a1:a10,false))

Etntered as an arry forumula ( with ctrl+Shift+Enter)
adapt the range to your needs

Using your posted data in A1:a11...here are two options:

This is a regular formula:

A12: =SUMIF(A2:A11,">0",A2:A11)/COUNTIF(A2:A11,">0")

This is ARRAY FORMULA is shorter...BUT you must remember to commit it
with CTRL+SHIFT+ENTER, instead of just ENTER:

A12: =AVERAGE(IF(A2:A12>0,A2:A12))

Does that help?

Yes!! thank you.
I've been asking fellow Excellers to help me solve problems/issues. Sometimes I end up helping them... but this time I just wasn't getting my thoughts together.
Thank you both. Next time I will look for the search tool, and try finding answers. I haven't used this kind of forum in 10 years. I've forgotten a lot about how it's used. If I keep coming back for answers I'll catch on.
Thanks again, Arthurbr, and Ron

Glad to hrlp.If your problem is solved please change the original post's title prefix to " solved"
thx