Excel - Pivot table not counting missing days in average

Question

I have a pivot table grouping by month and calculating the average of the values. Before grouping, a row of the table would look like that:

  |1May|2May|3May|4May|5May|
XX| 10 |    | 10 |    | 10 |

Note that, for the specific row, data are missing for 2May and 4May. Once I group this selection, the average for XX will be 10. My desired result would've been 6 instead, that is (10+0+10+0+10)/5.

Is there a way to do it, without installing software and without altering/adding columns to the input table?

Answer 1

The input data (which the pivot is based on) needs to have zeroes, not just blank cells