Offset behaviour/calculation order

[RaulSerg]

Hi, I have a comprehension question based on the attached illustration.
This doesn't make much sense to me.

Based on the column headers:

(i) Offset is based off Date (equals Date) but generates zeroes out of blank cells - I can kind of understand this, spilling functions do the same. But it's ignoring those zeroes in its calculation.
If I remember correctly, it has to do with calculation order: sort of like the zeroes are placed at the very end after the calculation is already performed.
However, here the zeroes are there before any calculation is made.
I find that confusing because the OFFSET-formula evaluates to: {10;0;0} and the AVERAGE-function is only applied separately and clearly afterwards.
So why is the result of Average3 not equal to Average2?

(ii) Even more confusing to me: Offset2 is based off Date2 (so the zeroes are really there) but it's giving different AVERAGE result, wrongly ignoring the zeroes. How does this make sense?

Clearly I am missing something?

[rorya]

To include the results of spill formulas, you'd need =AVERAGE(B7#) and =AVERAGE(D7#) for example. Currently you are just taking the averages of single cells.

[RaulSerg]

I swear I did check this and I just copy pasted everything :/

Anyway, thx.