Solved Excel formula to skip blank cells

February 22, 2010 at 11:34:40
Specs: Windows XP
Is there a formula I can use to automatically skip blank rows and return only the values of a particular cell(s) that have numbers in them?

See More: Excel formula to skip blank cells

Report •


✔ Best Answer
February 22, 2010 at 13:53:15
Assuming your example data is in A1:A9, place this in B1 and drag it down to B9. It will list the values in ascending order:

=SMALL($A$1:$A$9,ROW())

Now, once you run out of values (in your case - 3) you'll start to get a #NUM error.

To prevent that, use ISERROR as follows:

=IF(ISERROR(SMALL($A$1:$A$9,ROW())),"",SMALL($A$1:$A$9,ROW()))



#1
February 22, 2010 at 11:40:12
We're going to need a little more information before we can help.

An IF statement such as this will return the value in A1 if it's not empty and nothing if it is.

=IF(A1<>"",A1,"")

I don't if that is what you are looking for, so please give us a little more detail.


Report •

#2
February 22, 2010 at 11:48:26
Sorry. So in short I have a column that looks like this

12345
(blank)
(blank)
12346
(blank)
(blank)
(blank)
(blank)
12347

and I'm trying to get it to look like this

12345
12346
12347


Report •

#3
February 22, 2010 at 11:52:06
Why not just use Sort Ascending?

Report •

Related Solutions

#4
February 22, 2010 at 13:16:48
I could do that, but I was trying to filter a spreadsheet into another one and I'm trying to do so all in one step.

Report •

#5
February 22, 2010 at 13:53:15
✔ Best Answer
Assuming your example data is in A1:A9, place this in B1 and drag it down to B9. It will list the values in ascending order:

=SMALL($A$1:$A$9,ROW())

Now, once you run out of values (in your case - 3) you'll start to get a #NUM error.

To prevent that, use ISERROR as follows:

=IF(ISERROR(SMALL($A$1:$A$9,ROW())),"",SMALL($A$1:$A$9,ROW()))


Report •

#6
February 22, 2010 at 14:18:34
That works! However, my row 1 is going to have a header so if I paste that formula in row 2 it excludes the first number in the original column I am referencing. I'll try and work it out. Thanks so much.

Report •

#7
February 22, 2010 at 16:10:47
Row() returns an integer value that represents the row that the Row() function is in. Since you are starting in Row 2, you just need to adjust it a bit.

In Row 2, Row() will return 2, so just use Row()-1 to have it evaluate to 1.


Report •

Ask Question