Calculate average sum from a dataset?

Use this

Formula:

Please Login or Register  to view this content.

Your problem is that the cells with zero in are being counted in the Average. For example, A2:K2 Average is 0+0+0+0+5+0+5+0+0+0+0=10 divided by number of cells (10), giving 1, not the 5 you want.
The solution is to use AverageIf to ignore the cells with 0 in them:

Formula:

Please Login or Register  to view this content.

Edit: you need <>0 not just >0 to deal with negative numbers like you have in row 4.
Edit2: adjust the formatting of the answers to give the number of decimal places you want.

Sorry, I missed the negatives. Aardigspook's will do it.
As an alternative

Formula:

Please Login or Register  to view this content.

Originally Posted by QuantEdge

thx guy's this really helps... *** stars all round)

Stars do nothing for us. Instead click on Add Reputation.

Glad we could help.

If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.