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 here:
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" <
[email protected]> wrote in message
news:
[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>>
Bookmarks