How to average values from specific cells which are not empty and not zero?

user_15219481 0
Viewed 43

I have a table for conducting my family budget. I would like to have a function that shows average for specific cells like this

=AVERAGE(F11;M11;S11;Y11;AE11;AK11;AQ11;AW11;BC11;BI11;BO11;BU11)

but it should not consider EMPTY or "0" cells. I get zeroes when I pre-create a table with =sum(A4:A7) which returns ZERO.

This means that I need to see average for expenses that I make but only for months that are valid.

Please help, I am exhausted without finding an answer.

Table looks like this:

Family budget example

div0 error

google-sheets-formula google-sheets
1 Answers

use:

=IFERROR(AVERAGEIFS(A1:C1; A1:C1; "<>"; A1:C1; "<>0"))

enter image description here

also, to not get 0 by sum you can do:

=IFERROR(1/(1/SUM(A4:A7)))
user_56326290
Related