Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic named range across multiple sheets | Excel Discussion (Misc queries) | |||
Question regarding dynamic range setting | Excel Worksheet Functions | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Dynamic Print Range Help | Excel Worksheet Functions | |||
Add a Dynamic Range with 2 Conditions Q | Excel Worksheet Functions |