How to denote an average of data satisfying a given condition

Question

I have to write an expression for the following. I have values, one fpr each of the last 5 months:

Month: 1 - 989
Month: 2 - 0
Month: 3 - 234
Month: 4 - 0
Month: 5 - 098

I need to the calculate average value from these last 5 months which should be calculated only for non zero values. How can I write a common formula for that?

Thanks.

Answer 1

Since an average is a quotient of a sum and the number of samples (which we can itself write as sum), one way to encode this to incorporate the condition into the sum. For example, if we denote the month by $a$ and the value for the month $a$ by $v_a$, we can write $$\text{(average)} = \frac{\sum_{v_a \neq 0} v_a}{\sum_{v_a \neq 0} 1}.$$ The denominator here is just the number of months with nonzero values $v_a$.

Of course, the condition $v_a \neq 0$ is unnecessary for the sum in the numerator, as removing it simply includes more zero terms, which don't contribute to the sum. Also, this notation leaves implicit the index set of months $a$.

Answer 2

Let $m_i$ be non-negative integers such that $1 \leq i leq 5$. Let $P$ be the set of $m_i$ such that $m_i > 0$ i.e. $P = \{ m_i | m_i > 0 \}$. The average can be written as $$ A(m) = \frac{1}{|P|} \sum_{m_i \in P} m_i $$ where $A(m)$ is defined to be something for the empty set.

Answer 3

Let $f : \mathcal{M} \to \{0,1\}$ be defined as $$f(m) = \begin{cases}1 &\text{if } \mathrm{value}(m) \neq 0 \\ 0 &\text{otherwise}\end{cases},$$

then your average is $$\frac{\sum_{m \in \mathcal{M}}\mathrm{value}(m)}{\sum_{m \in \mathcal{M}}f(m)}.$$

When you write it like below (which gives the same result because of how $f$ is defined):

$$\frac{\sum_{m \in \mathcal{M}}\mathrm{value}(m) \cdot f(m)}{\sum_{m \in \mathcal{M}}f(m)}.$$

you get basically a weighted average where non-zero values get weight $1$ and zeros get weight $0$.

When implementing this in a spreadsheet, you have several options (here assuming Google Docs, but in other tools it is similar). Suppose your data is in column A, that is in range A1:A100, then you can use =AVERAGEIF(A1:A100, "<>0") or =SUM(A1:A100)/COUNTIF(A1:A100, "<>0").

Moreover, if you would fill B1:B100 with =IF(A1<>0, 1, 0) and similar, you can do also =SUM(A1:A100)/SUM(B1:B100) or just the weighted average as =SUMPRODUCT(A1:A100, B1:B100)/SUM(B1:B100).

I hope this helps $\ddot\smile$