How to make a distance counter between numbers in Excel?

Question

excel

How can one formula calculate the distance from one number to another, automatically determining the number or an empty cell?

spoiler

I used this formula = COUNT EMPTY. This works, but with one range set manually.
Is it possible to do something in the range (A2: A1001) to find numbers and the distance between them. And the distance between each number was displayed in a separate cell, and then each distance using = AVERAGE to find out the average value. Or do it with a macro?

Answer 1

1) Create a column with a sign that there is text in the first column
=ЕСЛИ(A1="";"";"!")
2) We are looking for the number of the next line in which there is a "!"
For the first line:
=ПОИСКПОЗ("!";B1:B1000;0)
For the next:

=ПОИСКПОЗ("!";ДВССЫЛ("R"&(C1+1)&"C[-1]:R10000C[-1]";0);0)+C1

3) Calculate the distance
=C2-C1-1
4) Calculate the average
=СРЗНАЧ(D1:D1000)
All this can be seen in the demo table