MrExcel Publishing
Your One Stop for Excel Tips & Solutions

filter data to graph


Posted by laura jean on February 05, 2002 8:01 AM

if i have cells:
x=1
y=2
z=-3
vol=13
x=-2
y=5
z=0.1
vol=20
etc...
how do I extract the number value from the vol cells,
and then plot just the vol cells?


Posted by Mark W. on February 05, 2002 8:11 AM

Suppose cells A2:A9 contains...

{"x=1"
;"y=2"
;"z=-3"
;"vol=13"
;"x=-2"
;"y=5"
;"z=0.1"
;"vol=20"}

Enter, =RIGHT(A2,LEN(A2)-FIND("=",A2))+0, into
cell B2 and Fill down to cell B9. Filter column A
where the value "begins with" "vol". Graph the
contents of column B.

Posted by Brian on February 05, 2002 8:16 AM

This formula should extract the number from the cell (I am assuming there is always an equals in the formula, and that the vol=20 is in cell A1

=VALUE(RIGHT(A1,LEN(A1)-FIND("=",A1)))

to then select non-adjacent rows or columns, select the first row or column as normal, then hold down CTRL while selecting the second.


Posted by laura jean on February 05, 2002 10:33 AM

OK! this works but when I graph the contents of
column B, the zeros appear. How do I plot only
non-blank cell contents???

Posted by laura jean on February 05, 2002 10:48 AM

(The "Plot visible cells only" Option is not
clearing the zero points for blank cells)

Posted by Mark W. on February 05, 2002 11:00 AM

Only graph the column B:B range (B2:B9 in my
example) that contains the results of the formula,
=RIGHT(A2,LEN(A2)-FIND("=",A2))+0.