last nonbank celll

[Manuel Murieta]

I would like the value of the cells in column A to be equal to the value
of the previous nonblank value in column A +1.

For example:


A
1 1
2 2
3
4
5
6 3
7
8
9
10 4
11
12
13
14
15
....
20
21 5

What formula could I place in the cell to add 1 to the value of the
previous non-blank cell in column A

 

[Roger Govier]

Hi Manuel

=LOOKUP(99^99,$A$1:A20)+1
would give 5 in A21, with the data that you show.

It is not a formula that can be copied down the column, however, as it
would fill very cell with the next number in the sequence.

 

[Guest]

Assuming your example is accurate the consecutive numbers are separated by
blanks...
How about using the value of the Next Non-blank cell?

Try this:
Select the range of numbers and blanks
Press the [F5] key......a shortcut for <edit><goto>
Click the [Special] button and Check: Blanks
Click the [OK] button

Next....while the blank cells are selected
Type: =
Press the Down Arrow key 1 time
Hold down the [Ctrl] key and Press [Enter]

That's all.

Example (using part of your posted data)
That method converts this:
1
2
(blank)
(blank)
(blank)
3
(blank)
(blank)
(blank)
4

To this:
1
2
3
3
3
3
4
4
4
4

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP

 

[Ragdyer]

As you can tell by the *completely different* suggestions of Roger and Ron,
your question is not at all clear!

I for one, am completely in the dark, as to what you're looking for.
Care to clarify?