excelexcel-formula

Calculate average and ignore zero values when data is not structured in a range

A B C
1 10
2
3 0
4
5
6 5 7.5
7

In Cell C6 I want to calculate the average of different numbers that are in various cells in the sheet.
In the example these are the cells A1, A3, B6.
Additionally, I want that in the average calculation cells with a 0 are ignored.
Therefore, in the example only Cell A1 and B6 should be considered --> C6 = (10+5)/2 = 7.5.

The issue is that these values are not organized in a column or row and therefore I am not able to use the =AVERAGEIF function. I tried to apply the solution from here but instead of 7.5 it returns 15 so it sums up the values and does not build the average.

=SUM(A1+A3+B6)/INDEX(FREQUENCY((A1+A3+B6);0);2)

Do you have any idea how to solve this issue?

Note:
I know in the example above I could solve the issue by simple not include Cell A3 into the average calculation. However, in my original file this number can always change so it can be either 0 or some other number. In case it is not 0 it should be included in the average calculation.

Solution

  • AVERAGEIF would work fine in your given example:

    =AVERAGEIF(A1:B6,"<>0")

    Result:

    enter image description here

    However if you can't use range for some reason and what to pick cell by cell you could use something like this:

    =LET(a,VSTACK(B4,B7,B10,B12,G4,G5,G9),SUM(a)/SUM(1*(a<>0)))

    Result:

    enter image description here