last nonbank celll

M

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
 
R

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.
 
G

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
 
R

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top