Use formula in averageifs

[Marco1]

Can I compute an average only if values are numbers?
I have a column with some numbers that, for some reasons, might be “”. I want to compute the average for those values, “grouped” by date (might be unordered) but only if those are actual values. I want to skip the “”.

|        A   |  B |
| 2020-01-01 |  5 |
| 2020-01-01 |  4 |
| 2020-01-02 |  8 |
| 2020-01-04 | "" |
| 2020-01-05 | 42 |
| 2020-01-08 |  2 |
| 2020-01-10 | 20 |

I’m currently doing =IF(ISNUMBER(A2),ROUND(AVERAGEIF(B:B,"<="&A2,B:B),0),"") but I would like to add a condition on B, inside the AVERAGEIFS, something like ISNUMBER(B2),B:B.

It doesn’t work. Is it possible? How?

[erAck]

AVERAGE() and AVERAGEIFS() already ignore empty cells and cells with text content. You can observe by changing an empty or text cell to numeric 0 then the result changes. An additional ISNUMBER(B2) or similar condition is unnecessary here. However, your AVERAGEIFS() doesn’t make sense as it compares number values in column B against a date value in column A. You probably meant A:A instead:

=IF(ISNUMBER(A2);ROUND(AVERAGEIF(A:A;"<="&A2;B:B);0);"")

[Marco1]

Yes, sorry. My fault: I’m using LO in Italian so I also had to translate formulas and columns to create a simple example… and I did it wrong.

So, better explanation:

|      F     |  K |  L  |
|    Done    | LT | mLT |
| 2020-02-19 |  5 |  5  |
| 2020-02-20 |  4 |  4  |
| 2020-02-20 |  3 |  4  |
| 2020-02-25 |  6 |  4  |
| 2020-02-25 |  1 |  4  |
| 2020-02-26 |  3 |  4  |
| 2020-02-27 |    |  4  |
| 2020-02-27 |  1 |  4  |
| 2020-02-28 |  3 |  4  |
| 2020-03-02 |  5 |  4  |

Column LT is a formula by itself that checks other data. What I want to do, is the correct completion of mLT column as moving average of the LT one, ordered by date.

Current formula, in column L is:

=IF(ISNUMBER(F2);ROUND(AVERAGEIF(F:F;"<="&F2;K:K);0);"")

and I want to be sure that average won’t consider empty LTs.

I’m gonna check as per your suggestion. It might work.

[Marco1]

It seems it doesn’t work. By simply removing the ROUND, my expectation was that the average won’t change, for a line with LT empty (""), but instead it does.

| LT |     average      |
|  5 | 5                |
|  4 | 4                |
|  3 | 4                |
|  2 | 3,5              |
|  6 | 4                |
|  6 | 4,57142857142857 |
|  6 | 4,57142857142857 |
|  6 | 4,33333333333333 |
|  1 | 4,33333333333333 |
|  3 | 4,2              |
|  	 | 3,90909090909091 |
|  1 | 3,90909090909091 |
|  3 | 3,83333333333333 |
|  5 | 3,92307692307692 |
|  3 | 3,6875           |
|  4 | 3,6875           |
|  1 | 3,6875           |
|  2 | 3,58823529411765 |
|  3 | 3,55555555555556 |

That’s what I’d like to avoid.

[Marco1]

No, sorry. You’re right. It works! That was a date problem: The empty LT and its next line had the same date, so the average had to be lower.