Dynamic Ranges - Dynamic Range to two data sets

[terrarium]

Hi Folks,


I've graphing actual budgets against planned budgets. I wanted to use a dynamic range but I am having trouble defining the bounds. In this example, if I applied the dynamic range to only "actual" it would not capture the last outlier in "planned". If i applied the dynamic range to only planned, it would not capture the first value in actual.


https://docs.google.com/spreadsheet/ccc?key=0AjnKiL-gi9AtdEs4MFNkdFZ6YkhVX0x1djFhd2Vfa2c&hl=en_US


Is there way to define these bounds with out reading the data points where both sets equal zero?


Thanks for the help and I'm learning alot. Soon now i'll be able to use the dashboard for my reporting measures, but this is one trouble I'm having with my graphs.


Thanks!

 

[Mike86]

Just define two named ranges along the lines of:


=OFFSET(Sheet1!D1,0,0,1,COUNTA(Sheet1!$1:$1)-2)


and


=OFFSET(Sheet1!C2,0,0,1,COUNTA(Sheet1!$2:$2)-3)


If your data is as shown (offset leading and trailing 0s), you can start the planned and actual offset reference cells in different columns to compensate for the 0's. Then change the "-#" value as needed to remove the trailing zeros. Make sure the number of values match in the two ranges.


This should let the planned values come from columns D-J and the actuals come from C-I.

 

[Hui]

Terrarium,


Firstly, Welcome to the Chandoo.org Forums


I'd use a few Named Forumla


mymin: =MIN(MATCH(1,--(Sheet1!$B$4:$K$4>0),0),MATCH(1,--(Sheet1!$B$5:$K$5>0),0))

mymin is the position of the first data >0 in your range B4:K5 = 2


mymax: =MAX(MAX(IF(Sheet1!$B$4:$K$4<>0,COLUMN(Sheet1!$B$4:$K$4)-1)),MAX(IF(Sheet1!$B$5:$K$5<>0,COLUMN(Sheet1!$B$5:$K$5)-1)))

mymin is the position of the last data >0 in your range B4:K5 = 9


myPlanned: =OFFSET(Sheet1!$A$4,,myMin,1,myMax-myMin+1)

myActual: =OFFSET(Sheet1!$A$5,,myMin,1,myMax-myMin+1)

 

[terrarium]

Thanks for the welcome and thanks for the help. You have really helped people out a lot here. I knew that it was going to be some min max function but i wasn't sure. This looks like it will work.


Will look for other users to help out.