How do I get a cell to display the distance to the next filled cell above?

[abaabaabaaba4]

Hi
So basically I was wondering if there is something I could put into a cell that would show me the number of blank cells above it before the next filled cell so for example
o=empty cell
x=filled cell
Instead of
oooxooo
oxoooox
ooooooo
oooxoox
oxooooo
I would have
oooxooo
oxoooox
ooooooo
ooo3oo2
o3ooooo

For the pieces of data at the top I obviously cant have numbers there as there are no filled cells above them but there is a lot of vertical data where the formula would be used so it would be very helpful if someone could help me.
Thanks in advance.

 

[ITWare2008]

Hi there,

Could you explain what means the 3 and 2 on row 4 and row 5, and by the way why on 4th and 7th position of row 4, 2nd position of row 5?

Vândalo

 

[abaabaabaaba4]

Thanks for taking the time to reply, going left and right is just for a non relevant variable so maybe it would better for the sake of simplicity I just use 1 column in the example. The 3 and the 2 are the distances to the next filled cell above so I want something that when I put this in it will automatically work that out and show the number.
So for the revised example:
x=filled cell
o=empty cell
Example 1)Example 2)Example 3)
For
o o o
x x x
o o x
o x o
x o o
I would have
o o o
x x x
o o 1
o 2 o
3 o o

 

[ITWare2008]

Let's try this

at the empty cell press alt+shift+= it will automatically write the formula sum(firstcell:lastcell_aboveyourcell). Just replace de sum function with countblank and keep range assumed by the formula plus 1

You will have =countblank(firstcell:lastcell_aboveyourcell)+1

I guess you will achieve your goal.

Vândalo

 

[abaabaabaaba4]

The command didn't work but I found the auto sum button and it sought of worked, it selected upwards but on cells that were below all the data but when I used it on the cells I wanted it selected the region to the left. I think I'll just manually enter in the distance because this has to have cell locations and be custom to each cell so I may as well just count, thanks anyway for your time.

 

[Peter_SSs]

Welcome to the MrExcel board!

Are the "filled" cells ...
- always filled with numbers,
- always filled with text,
- could be filled with either numbers or text?

 

[abaabaabaaba4]

I have manually entered all the data so instead if x's it's all numbers now but I would still like to know if there is a formula for when I have to enter in new data.

 

[Peter_SSs]

... instead if x's it's all numbers ...

So, is this what you mean?

Formula copied across

Excel Workbook
	D	E	F	G
1	7	5
2		5	4
3	3	5
4
5		5
6		5
7		5
8	5	1	6	#N/A
Distance above