Dynamic Charts Offset

[Guest]

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.

 

[Jon Peltier]

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/
_______

 

[Guest]

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]

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,">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/
_______

 

[akullen]

Hi Jon

This was a very nice thread for me to read. It solved a problem I ha
perfectly, almost. When doing it interactivly it works perfect, but
need to create it in VBA and that only works up until connecting th
names to the chart.

I tried to record it and then the macro used the charts series.xvalu
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 Where:=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 coul
solve it?

Thank you in advance
Anders.