how do you take the average of an entire column, eliminating fields that are blank

[janastead]

i have 35 rows. if only 5 have data, i need the average to just count the 5. right now it is averaging the blank fields as 0's.

=AVERAGE(K5:K35) is the current formula

[dilipandey]

Hi Janastead,

Use the following formula:-

{=AVERAGE(IF(K5:K35>0,K5:K35,""))}

Above is an array formula and need to be entered using Ctrl + Shift + Enter key combination. thanks


Regards,
DILIPandey

[janastead]

i tried it, and i received an error message about not being able to use an array in a merged cell. i removed the merge and now it just posts the formula in the cell itself as text instead of it becoming a formula. thank you for helping!

[haru]

If the array is giving you trouble try:

Please Login or Register  to view this content.

[janastead]

Hmm. That one says too few arguments. Any other ideas? Thank you so much!

[ConneXionLost]

Try:

Please Login or Register  to view this content.

Cheers,

[janastead]

that one works PERFECT. Thank you! the only thing is, it is not counting zeros OR blanks and i need the zeros to count. is that possible?

i have learned so much since joining this forum. i truley appreciate the help

[haru]

Please Login or Register  to view this content.

[janastead]

Again, I am learning so much. I changed the 0 to a -1 and it is working.

=SUM(K5:K35)/COUNTIF(K5:K35,">"&0) changed to =SUM(K5:K35)/COUNTIF(K5:K35,">"&-1)

Please let me know if you see any issues with the formula to the right. Thanks again!

[haru]

Look at the code above, with your code, if you were to have a value like -0.5 then it would be taken into the average, but we are looking only for positive numbers. Your formula will work as long as there are no negative numbers, but just change the

">" to ">="

which turns 'greater than' to 'greater than or equal to'

[Fotis1991]

Hi

Excuse me.....but what are you doing?

First formula of DILIPandey, first of huru(a little modified) and of course ConneXionLost, work nicely.

Am i missing something??

[janastead]

=sum(k5:k35)/countif(k5:k35,">="&0) and =SUM(K5:K35)/COUNTIF(K5:K35,">"&-1) are the 2 that worked for me. in your attachment the ones that yeilded 10 were not counting "0" the ones that work do. confusing for me, but i think i understand. thanks so much to everyone for the help!

[Fotis1991]

....If you say so... It's your project... But these 2 formulae, give the same result as(your first)ANERAGE formula...

=AVERAGE(K5:K35)

...

[janastead]

that one counted blanks as zero

[haru]

I think what Fotis is trying to say is that the AVERAGE function does not count blanks, but does include zeros. It a little confusing why =AVERAGE(K5:K35) wouldn't work properly.

[janastead]

lol, i must be doing something else wrong then. i'm sorry. hope the only one confused is me.