2

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

Sufficient Happiness
  • 39
  • 5

1 Answers1

1

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)))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Can you please also advise, how can I not have an div/0! error for lines where I have empty cells and average is calculated? Thank you in advance! At the moment I use =AVERAGE(F14;M14;T14;AA14;AH14;AO14;AV14;BC14;BJ14;BQ14;BX14;CE14) because I need to omit cells which are between these, as they contain info by weeks which is already summed up... – Sufficient Happiness Feb 16 '21 at 10:15
  • wrapping it into IFERROR should do the trick – player0 Feb 16 '21 at 10:17
  • 1
    @SufficientHappiness if you feel like you are ready to level up, see: https://stackoverflow.com/a/65435321/5632629 – player0 Feb 16 '21 at 10:25
  • 1
    documentation for the sake of completeness: https://support.google.com/docs/answer/3256534 and https://support.google.com/docs/answer/3093304 – Entara Feb 16 '21 at 10:33
  • Thank you guys! @player0, I checked your link - it is very cool what formulas can do. However I can't understand it yet. – Sufficient Happiness Feb 16 '21 at 20:50