excel: tricky formula/function question

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

howdy, all :) maybe someone can help me.

i have an interesting "problem" and maybe someone has the answer. i am using
excel.

i am trying to figure out a formula/function which can convert a small set
of numbers into a different set of numbers without manually using the "sort"
function. here is an example:

i have one column of seven rows with the following values:

x
31.5
x
32.0
34.1
x
29.5

i want to convert that into a different column, using formulas/functions so
that the new column reads:

31.5
32.0
34.1
29.5
(blank)
(blank)
(blank)

basically, i want to take one column of values, and eliminate all
non-numerical numbers, and place them in the new column with all the numbers
at the top, but not sorted by value, just by the order that they appear. so,
i would have side by side columns like this:


x 31.5
31.5 32.0
x 34.1
32.0 29.5
34.1
x
29.5

if anyone has a clue how or if that can be done, that would be great!

thanks much!
scott :)
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER) in cell
B1
=INDEX($A$1:$A$100,SMALL(IF(ISNUMBER($A$1:$A$100),ROW($A$1:$A$100)),ROW
(1:1)))
and copy this down
 
Hello, Frank! Brilliant! It works great! I'm trying desperately to
understand how it works without luck, but I'm glad it works.

One more quick question without wanting to ask for too much:

The formulas work wonderfully well. However, I tried the formula on data
from X3:X8 instead of A1:A6. I've adjusted everything in the formula ok but
I'm having problems knowing what to put in the last ROW(?,?) portion of the
formula.

For example, in X3 through X8, I have:

x
31.50
x
32.00
x
30.50

In Y3, I put the following:

=INDEX($X$3:$X$8,SMALL(IF(ISNUMBER($X$3:$X$8),ROW($X$3:$X$8)),ROW(1:1)))
(then CTRL+SHIFT+ENTER) for an array, then copied them down through Y8.

This gives me:

32.00
30.50
#REF!
#NUM!
#NUM!
#NUM!

instead of what it should be:

31.50
32.00
30.50
#NUM!
#NUM!
#NUM!

Any thoughts? :) Also, is there a way to convert those #NUM! values to
(blank) in that function?

Thank you!
Scott :):)
 
How about:

=INDEX($X$3:$X$10,SMALL(IF(ISNUMBER($X$3:$X$10),ROW($X$3:$X$10)-ROW($X$3)+1),
ROW(1:1)))

(still array entered and all one cell)

And if this is for display only, I'd use Format|Conditional formatting with a
formula like:

=iserror(a1)
with the same color font as fill (white on white?).
(Use the activecell's address in the formatting formula)

If you really need to display a blank, I'd use helper column (and hide that
column with the error):

=IF(ISERROR(a1),"",a1)
 
Thanks to you and Frank for all your help! Both suggestions have worked
quite well and I'm very pleased.

Thanks much!
Scott :)

P.S. As a side note, I did figure out the formula but I used a helper
column, so I kinda cheated in a sense, but got it to work another way. But,
much preferred to do it without the helper :)
 
Back
Top