| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Offset
Please help. I have read other posts but my situation is a bit different and
I am having trouble adapting the suggestions to my situation. I am creating a bar chart that shows per contract the estimated hours and the actual hours. CUrrently, I have contracts that have no estimated and actual hours, so I do not want to display these contract on the chart, I only want to show those contracts that have data. I do not want to use auto filter to exclude those contracts with zero values. I used the suggestions from the user community and populate zero values with #N/A via a formula when the value is zero, thinking this would eliminate these from the chart, but it does not. I tried using OFFSET, but I can't get that to work either because I have more than one column and I am displaying the contracts in a specific order. Here is an example of my data, and since fed from another spreadsheet, additional estimated and actual values could be populated: 2006 YTD 2006 YTD Estimated Actual CIG #N/A #N/A CIB #N/A #N/A DRS #N/A #N/A FTA 234 162 IPS #N/A #N/A SIR #N/A #N/A Men 156 412 Mon #N/A #N/A NYCA #N/A #N/A NYCB #N/A #N/A NYCP #N/A #N/A NYC 453 652 NYCW #N/A #N/A NYSC #N/A #N/A NYSP #N/A #N/A NYSP6 #N/A #N/A So what I want to see on my bar chart is the 3 contracts that have values on the x axis, and the corresponding estimated and actuals on the y axis. Then as additional contracts have values in the estimated and actual columns, they too will show up on the bar chart. What I see now is all the contracts, regardless of whether there is a value in the estimated and actual hours. |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic Charts Offset
The #N/A only simulates a non-entry in a line or XY series, but not in a
bar, column, or area chart. Neither #N/A, zero, nor a true blank cell prevent a point from taking space in the chart. The chart series includes it as a point, even if it doesn't "appear". In your case, you will see the #N/A projects as categories in the chart. What you need to do is use some formulas in a second range that extract plottable data, and use this second range as the chart source. For example, if I put your data into A1:C18, I can add a few columns and get something to plot: 2006 YTD 2006 YTD Est Actual Est Actual CIG 0 0 14 NYC 453 652 CIB 0 0 9 Men 156 412 DRS 0 0 6 FTA 234 162 FTA 234 162 0 #REF! #REF! #REF! IPS 0 0 0 #REF! #REF! #REF! SIR 0 0 0 #REF! #REF! #REF! Men 156 412 0 #REF! #REF! #REF! Mon 0 0 0 #REF! #REF! #REF! NYCA 0 0 0 #REF! #REF! #REF! NYCB 0 0 0 #REF! #REF! #REF! NYCP 0 0 0 #REF! #REF! #REF! NYC 453 652 0 #REF! #REF! #REF! NYCW 0 0 0 #REF! #REF! #REF! NYSC 0 0 0 #REF! #REF! #REF! NYSP 0 0 0 #REF! #REF! #REF! NYSP6 0 0 0 #REF! #REF! #REF! Cell D3 contains this array formula (entered using CTRL+SHIFT+ENTER, not just ENTER): {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)0)*(($C$3:$C $18)0),ROW()-ROW($D$2))} Don't type the curly brackets, Excel puts them there if you've correctly entered the array formula. What it does is find the rows that have non-zero data (use zeros or blanks in the range, not #N/A). This formula is filled downwards as far as needed. Cell E3 has this regular formula: =OFFSET(A$1,$D3-1,0) This is filled right and left as far as needed. It picks out the value for the row in column D. You can set up dynamic ranges for columns E:G, and use these in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CSK" wrote in message ... Please help. I have read other posts but my situation is a bit different and I am having trouble adapting the suggestions to my situation. I am creating a bar chart that shows per contract the estimated hours and the actual hours. CUrrently, I have contracts that have no estimated and actual hours, so I do not want to display these contract on the chart, I only want to show those contracts that have data. I do not want to use auto filter to exclude those contracts with zero values. I used the suggestions from the user community and populate zero values with #N/A via a formula when the value is zero, thinking this would eliminate these from the chart, but it does not. I tried using OFFSET, but I can't get that to work either because I have more than one column and I am displaying the contracts in a specific order. Here is an example of my data, and since fed from another spreadsheet, additional estimated and actual values could be populated: 2006 YTD 2006 YTD Estimated Actual CIG #N/A #N/A CIB #N/A #N/A DRS #N/A #N/A FTA 234 162 IPS #N/A #N/A SIR #N/A #N/A Men 156 412 Mon #N/A #N/A NYCA #N/A #N/A NYCB #N/A #N/A NYCP #N/A #N/A NYC 453 652 NYCW #N/A #N/A NYSC #N/A #N/A NYSP #N/A #N/A NYSP6 #N/A #N/A So what I want to see on my bar chart is the 3 contracts that have values on the x axis, and the corresponding estimated and actuals on the y axis. Then as additional contracts have values in the estimated and actual columns, they too will show up on the bar chart. What I see now is all the contracts, regardless of whether there is a value in the estimated and actual hours. |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic Charts Offset
I get to the point where columns D to G match your sample, but when I create
the bar chart, the #REF! in column E is on the x axis. So what I see is NYC hours, Men hours and FTA hours, then #REF! 13 more times. How do I set up my chart to not plot beyond where there are hours? "Jon Peltier" wrote: The #N/A only simulates a non-entry in a line or XY series, but not in a bar, column, or area chart. Neither #N/A, zero, nor a true blank cell prevent a point from taking space in the chart. The chart series includes it as a point, even if it doesn't "appear". In your case, you will see the #N/A projects as categories in the chart. What you need to do is use some formulas in a second range that extract plottable data, and use this second range as the chart source. For example, if I put your data into A1:C18, I can add a few columns and get something to plot: 2006 YTD 2006 YTD Est Actual Est Actual CIG 0 0 14 NYC 453 652 CIB 0 0 9 Men 156 412 DRS 0 0 6 FTA 234 162 FTA 234 162 0 #REF! #REF! #REF! IPS 0 0 0 #REF! #REF! #REF! SIR 0 0 0 #REF! #REF! #REF! Men 156 412 0 #REF! #REF! #REF! Mon 0 0 0 #REF! #REF! #REF! NYCA 0 0 0 #REF! #REF! #REF! NYCB 0 0 0 #REF! #REF! #REF! NYCP 0 0 0 #REF! #REF! #REF! NYC 453 652 0 #REF! #REF! #REF! NYCW 0 0 0 #REF! #REF! #REF! NYSC 0 0 0 #REF! #REF! #REF! NYSP 0 0 0 #REF! #REF! #REF! NYSP6 0 0 0 #REF! #REF! #REF! Cell D3 contains this array formula (entered using CTRL+SHIFT+ENTER, not just ENTER): {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)0)*(($C$3:$C $18)0),ROW()-ROW($D$2))} Don't type the curly brackets, Excel puts them there if you've correctly entered the array formula. What it does is find the rows that have non-zero data (use zeros or blanks in the range, not #N/A). This formula is filled downwards as far as needed. Cell E3 has this regular formula: =OFFSET(A$1,$D3-1,0) This is filled right and left as far as needed. It picks out the value for the row in column D. You can set up dynamic ranges for columns E:G, and use these in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CSK" wrote in message ... Please help. I have read other posts but my situation is a bit different and I am having trouble adapting the suggestions to my situation. I am creating a bar chart that shows per contract the estimated hours and the actual hours. CUrrently, I have contracts that have no estimated and actual hours, so I do not want to display these contract on the chart, I only want to show those contracts that have data. I do not want to use auto filter to exclude those contracts with zero values. I used the suggestions from the user community and populate zero values with #N/A via a formula when the value is zero, thinking this would eliminate these from the chart, but it does not. I tried using OFFSET, but I can't get that to work either because I have more than one column and I am displaying the contracts in a specific order. Here is an example of my data, and since fed from another spreadsheet, additional estimated and actual values could be populated: 2006 YTD 2006 YTD Estimated Actual CIG #N/A #N/A CIB #N/A #N/A DRS #N/A #N/A FTA 234 162 IPS #N/A #N/A SIR #N/A #N/A Men 156 412 Mon #N/A #N/A NYCA #N/A #N/A NYCB #N/A #N/A NYCP #N/A #N/A NYC 453 652 NYCW #N/A #N/A NYSC #N/A #N/A NYSP #N/A #N/A NYSP6 #N/A #N/A So what I want to see on my bar chart is the 3 contracts that have values on the x axis, and the corresponding estimated and actuals on the y axis. Then as additional contracts have values in the estimated and actual columns, they too will show up on the bar chart. What I see now is all the contracts, regardless of whether there is a value in the estimated and actual hours. |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic Charts Offset
I advised:
You can set up dynamic ranges for columns E:G, and use these in the chart. but I didn't say how, did I? There are lots of references for dynamic charts. You can start looking he http://peltiertech.com/Excel/Charts/Dynamics.html You need to set up some dynamic ranges. On the Insert menu, select Names, then Define. In the Name box type a name, like 'Labels', and in the Refers To box enter a formula like =OFFSET(E3,0,0,COUNTIF(D:D,"0"),1) Click Add, then repeat for these additional names: 'Est' =OFFSET(Labels,0,1) 'Actual' =OFFSET(Labels,0,2) Start the chart wizard, pick a chart type in step 1, and in step 2 select the Series tab. Click Add, and in the Category Labels box, type =Sheet1!Labels (substituting the name of your sheet), then in the Values box, type =Sheet1!Est. Click Add again, and in the Values box, type =Sheet1!Actual. Finish the wizard, and admire your chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CSK" wrote in message ... I get to the point where columns D to G match your sample, but when I create the bar chart, the #REF! in column E is on the x axis. So what I see is NYC hours, Men hours and FTA hours, then #REF! 13 more times. How do I set up my chart to not plot beyond where there are hours? "Jon Peltier" wrote: The #N/A only simulates a non-entry in a line or XY series, but not in a bar, column, or area chart. Neither #N/A, zero, nor a true blank cell prevent a point from taking space in the chart. The chart series includes it as a point, even if it doesn't "appear". In your case, you will see the #N/A projects as categories in the chart. What you need to do is use some formulas in a second range that extract plottable data, and use this second range as the chart source. For example, if I put your data into A1:C18, I can add a few columns and get something to plot: 2006 YTD 2006 YTD Est Actual Est Actual CIG 0 0 14 NYC 453 652 CIB 0 0 9 Men 156 412 DRS 0 0 6 FTA 234 162 FTA 234 162 0 #REF! #REF! #REF! IPS 0 0 0 #REF! #REF! #REF! SIR 0 0 0 #REF! #REF! #REF! Men 156 412 0 #REF! #REF! #REF! Mon 0 0 0 #REF! #REF! #REF! NYCA 0 0 0 #REF! #REF! #REF! NYCB 0 0 0 #REF! #REF! #REF! NYCP 0 0 0 #REF! #REF! #REF! NYC 453 652 0 #REF! #REF! #REF! NYCW 0 0 0 #REF! #REF! #REF! NYSC 0 0 0 #REF! #REF! #REF! NYSP 0 0 0 #REF! #REF! #REF! NYSP6 0 0 0 #REF! #REF! #REF! Cell D3 contains this array formula (entered using CTRL+SHIFT+ENTER, not just ENTER): {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)0)*(($C$3:$C $18)0),ROW()-ROW($D$2))} Don't type the curly brackets, Excel puts them there if you've correctly entered the array formula. What it does is find the rows that have non-zero data (use zeros or blanks in the range, not #N/A). This formula is filled downwards as far as needed. Cell E3 has this regular formula: =OFFSET(A$1,$D3-1,0) This is filled right and left as far as needed. It picks out the value for the row in column D. You can set up dynamic ranges for columns E:G, and use these in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CSK" wrote in message ... Please help. I have read other posts but my situation is a bit different and I am having trouble adapting the suggestions to my situation. I am creating a bar chart that shows per contract the estimated hours and the actual hours. CUrrently, I have contracts that have no estimated and actual hours, so I do not want to display these contract on the chart, I only want to show those contracts that have data. I do not want to use auto filter to exclude those contracts with zero values. I used the suggestions from the user community and populate zero values with #N/A via a formula when the value is zero, thinking this would eliminate these from the chart, but it does not. I tried using OFFSET, but I can't get that to work either because I have more than one column and I am displaying the contracts in a specific order. Here is an example of my data, and since fed from another spreadsheet, additional estimated and actual values could be populated: 2006 YTD 2006 YTD Estimated Actual CIG #N/A #N/A CIB #N/A #N/A DRS #N/A #N/A FTA 234 162 IPS #N/A #N/A SIR #N/A #N/A Men 156 412 Mon #N/A #N/A NYCA #N/A #N/A NYCB #N/A #N/A NYCP #N/A #N/A NYC 453 652 NYCW #N/A #N/A NYSC #N/A #N/A NYSP #N/A #N/A NYSP6 #N/A #N/A So what I want to see on my bar chart is the 3 contracts that have values on the x axis, and the corresponding estimated and actuals on the y axis. Then as additional contracts have values in the estimated and actual columns, they too will show up on the bar chart. What I see now is all the contracts, regardless of whether there is a value in the estimated and actual hours. |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Dynamic Charts Offset
Hi Jon This was a very nice thread for me to read. It solved a problem I had perfectly, almost. When doing it interactivly it works perfect, but I need to create it in VBA and that only works up until connecting the names to the chart. I tried to record it and then the macro used the charts series.xvalue and series.value i.e. Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("N28") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=FileName.xls!Lables" ActiveChart.SeriesCollection(1).Values = "=FileName.xls!Values" ActiveChart.SeriesCollection(1).Name = "=""test""" ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" I did not get this to work so I tried using the formula i.e. xlChartSeries.Formula = "=SERIES(""test"",FileName.xls!Lables,FileName.xls !Values,1)" This does not work either. It work if a I put a range like "$A$1:$A$2" instead put that not what I want. Have you tried this and if so, do you have any proposals on how I could solve it? Thank you in advance Anders. Jon Peltier Wrote: I advised: You can set up dynamic ranges for columns E:G, and use these in the chart. but I didn't say how, did I? There are lots of references for dynamic charts. You can start looking he http://peltiertech.com/Excel/Charts/Dynamics.html You need to set up some dynamic ranges. On the Insert menu, select Names, then Define. In the Name box type a name, like 'Labels', and in the Refers To box enter a formula like =OFFSET(E3,0,0,COUNTIF(D:D,"0"),1) Click Add, then repeat for these additional names: 'Est' =OFFSET(Labels,0,1) 'Actual' =OFFSET(Labels,0,2) Start the chart wizard, pick a chart type in step 1, and in step 2 select the Series tab. Click Add, and in the Category Labels box, type =Sheet1!Labels (substituting the name of your sheet), then in the Values box, type =Sheet1!Est. Click Add again, and in the Values box, type =Sheet1!Actual. Finish the wizard, and admire your chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CSK" wrote in message ... I get to the point where columns D to G match your sample, but when I create the bar chart, the #REF! in column E is on the x axis. So what I see is NYC hours, Men hours and FTA hours, then #REF! 13 more times. How do I set up my chart to not plot beyond where there are hours? "Jon Peltier" wrote: The #N/A only simulates a non-entry in a line or XY series, but not in a bar, column, or area chart. Neither #N/A, zero, nor a true blank cell prevent a point from taking space in the chart. The chart series includes it as a point, even if it doesn't "appear". In your case, you will see the #N/A projects as categories in the chart. What you need to do is use some formulas in a second range that extract plottable data, and use this second range as the chart source. For example, if I put your data into A1:C18, I can add a few columns and get something to plot: 2006 YTD 2006 YTD Est Actual Est Actual CIG 0 0 14 NYC 453 652 CIB 0 0 9 Men 156 412 DRS 0 0 6 FTA 234 162 FTA 234 162 0 #REF! #REF! #REF! IPS 0 0 0 #REF! #REF! #REF! SIR 0 0 0 #REF! #REF! #REF! Men 156 412 0 #REF! #REF! #REF! Mon 0 0 0 #REF! #REF! #REF! NYCA 0 0 0 #REF! #REF! #REF! NYCB 0 0 0 #REF! #REF! #REF! NYCP 0 0 0 #REF! #REF! #REF! NYC 453 652 0 #REF! #REF! #REF! NYCW 0 0 0 #REF! #REF! #REF! NYSC 0 0 0 #REF! #REF! #REF! NYSP 0 0 0 #REF! #REF! #REF! NYSP6 0 0 0 #REF! #REF! #REF! Cell D3 contains this array formula (entered using CTRL+SHIFT+ENTER, not just ENTER): {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)0)*(($C$3:$C $18)0),ROW()-ROW($D$2))} Don't type the curly brackets, Excel puts them there if you've correctly entered the array formula. What it does is find the rows that have non-zero data (use zeros or blanks in the range, not #N/A). This formula is filled downwards as far as needed. Cell E3 has this regular formula: =OFFSET(A$1,$D3-1,0) This is filled right and left as far as needed. It picks out the value for the row in column D. You can set up dynamic ranges for columns E:G, and use these in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CSK" wrote in message ... Please help. I have read other posts but my situation is a bit different and I am having trouble adapting the suggestions to my situation. I am creating a bar chart that shows per contract the estimated hours and the actual hours. CUrrently, I have contracts that have no estimated and actual hours, so I do not want to display these contract on the chart, I only want to show those contracts that have data. I do not want to use auto filter to exclude those contracts with zero values. I used the suggestions from the user community and populate zero values with #N/A via a formula when the value is zero, thinking this would eliminate these from the chart, but it does not. I tried using OFFSET, but I can't get that to work either because I have more than one column and I am displaying the contracts in a specific order. Here is an example of my data, and since fed from another spreadsheet, additional estimated and actual values could be populated: 2006 YTD 2006 YTD Estimated Actual CIG #N/A #N/A CIB #N/A #N/A DRS #N/A #N/A FTA 234 162 IPS #N/A #N/A SIR #N/A #N/A Men 156 412 Mon #N/A #N/A NYCA #N/A #N/A NYCB #N/A #N/A NYCP #N/A #N/A NYC 453 652 NYCW #N/A #N/A NYSC #N/A #N/A NYSP #N/A #N/A NYSP6 #N/A #N/A So what I want to see on my bar chart is the 3 contracts that have values on the x axis, and the corresponding estimated and actuals on the y axis. Then as additional contracts have values in the estimated and actual columns, they too will show up on the bar chart. What I see now is all the contracts, regardless of whether there is a value in the estimated and actual hours. -- akullen ------------------------------------------------------------------------ akullen's Profile: http://www.excelforum.com/member.php...o&userid=32513 View this thread: http://www.excelforum.com/showthread...hreadid=512386 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Dynamic Charts | Charts and Charting in Excel | |||
| Problem with Slow ReCalculation of Dynamic Range Using OFFSET | Excel Worksheet Functions | |||
| dynamic charts | Charts and Charting in Excel | |||
| Can't create dynamic charts | Charts and Charting in Excel | |||
Linear Mode
