repost: plz help- dynamic range with gaps?
 

Excel 2003 on Win 2000

I have a range of cells that I need to graph; each cell in the range has a
formula, but may or may not have actual data (so I can use an IF statement
to leave it blank or N/A).

My (internal) customer wants a column graph that only shows X-axis points
for the cells that have values- e.g. I need to supress the blank values
completely. How can I do this? I'm thinking a named range, but I don't know
what formula will give me the desired results...


For example:
(raw data)
Cats 100
Dogs 150
Ferrets 0 (or NA)
Fish 50

my graph needs to show:

X
X X
X X X
Cats Dogs Fish

Help!
Thanks,
Keith
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Assuming that A2:B5 contains your data, and your formula for Column B
returns 0 when your IF statement is FALSE, try the following...

D2, copied down and over to the next column, Column E:

=IF(ROWS($D$2:D2)<=COUNTIF($B$2:$B$5,"0"),INDEX(A $2:A$5,SMALL(IF($B$2:$B
$50,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$2:D2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Then, define the following dynamic ranges...

Insert Name Define

Name: ChartLabels

Refers to:
=Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$65536,MATCH(2,1/(Sheet1!$D$2:$D$65536<
"")))

Click Add

Name: ChartValues

Refers to:
=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$65536,MATCH(2,1/(Sheet1!$D$2:$D$65536<
"")))

Click Ok

Lastly, change the chart's 'Data Source'...

1) Click on the column series to select it

2) In the 'Formula Bar', change the cell references to the named
ranges...

=SERIES(Sheet1!$E$1,Sheet1!ChartLabels,Sheet1!Char tValues,1)

(Sheet1!$E$1 contains the header/label for Column E.)

Hope this helps!

In article ,
"KR" wrote:

Excel 2003 on Win 2000

I have a range of cells that I need to graph; each cell in the range has a
formula, but may or may not have actual data (so I can use an IF statement
to leave it blank or N/A).

My (internal) customer wants a column graph that only shows X-axis points
for the cells that have values- e.g. I need to supress the blank values
completely. How can I do this? I'm thinking a named range, but I don't know
what formula will give me the desired results...


For example:
(raw data)
Cats 100
Dogs 150
Ferrets 0 (or NA)
Fish 50

my graph needs to show:

X
X X
X X X
Cats Dogs Fish

Help!
Thanks,
Keith

Wow, I'll both test this, and dissect it until I understand how it works!
Thanks a bunch,
Keith

"Domenic" wrote in message
...
Assuming that A2:B5 contains your data, and your formula for Column B
returns 0 when your IF statement is FALSE, try the following...

D2, copied down and over to the next column, Column E:

=IF(ROWS($D$2:D2)<=COUNTIF($B$2:$B$5,"0"),INDEX(A $2:A$5,SMALL(IF($B$2:$B
$50,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$2:D2))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Then, define the following dynamic ranges...

Insert Name Define

Name: ChartLabels

Refers to:
=Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$65536,MATCH(2,1/(Sheet1!$D$2:$D$65536<
"")))

Click Add

Name: ChartValues

Refers to:
=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$65536,MATCH(2,1/(Sheet1!$D$2:$D$65536<
"")))

Click Ok

Lastly, change the chart's 'Data Source'...

1) Click on the column series to select it

2) In the 'Formula Bar', change the cell references to the named
ranges...

=SERIES(Sheet1!$E$1,Sheet1!ChartLabels,Sheet1!Char tValues,1)

(Sheet1!$E$1 contains the header/label for Column E.)

Hope this helps!

In article ,
"KR" wrote:

Excel 2003 on Win 2000

I have a range of cells that I need to graph; each cell in the range has
a
formula, but may or may not have actual data (so I can use an IF
statement
to leave it blank or N/A).

My (internal) customer wants a column graph that only shows X-axis
points
for the cells that have values- e.g. I need to supress the blank values
completely. How can I do this? I'm thinking a named range, but I don't
know
what formula will give me the desired results...


For example:
(raw data)
Cats 100
Dogs 150
Ferrets 0 (or NA)
Fish 50

my graph needs to show:

X
X X
X X X
Cats Dogs Fish

Help!
Thanks,
Keith