Plotting graph with dynamic Column namedrange and grouped columns
- Thread starter sampahmel
- Start date
NARAYANK991
Excel Ninja
Hi ,
The COUNTA function only looks at whether cells are populated or not ; not whether they are grouped or not.
See your file.
Narayan
The COUNTA function only looks at whether cells are populated or not ; not whether they are grouped or not.
See your file.
Narayan
Hi Debraj,
The file is same as the one provided by
NARAYANK991 above.
The NamedRange formula provided by him/her is as follow:
=OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
&
=OFFSET(Sheet1!$K$4,0,3,1,COUNTA(Sheet1!$4:$4)-1)
Something which I don't quite get it, why is it that "Real_GDP_by_Expenditure_in_SGD_mil" is in the first offset formula? Is this necessary if I want to plot the second OFFSET formula against the first offset formula?
The file is same as the one provided by
NARAYANK991 above.
The NamedRange formula provided by him/her is as follow:
=OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
&
=OFFSET(Sheet1!$K$4,0,3,1,COUNTA(Sheet1!$4:$4)-1)
Something which I don't quite get it, why is it that "Real_GDP_by_Expenditure_in_SGD_mil" is in the first offset formula? Is this necessary if I want to plot the second OFFSET formula against the first offset formula?
NARAYANK991
Excel Ninja
Hi ,
Can you comment on this file ?
Narayan
Can you comment on this file ?
Narayan
Hi Narayank991,
That looks awesome. I tried to insert columns on the to add 4Q2008 and and 4Q2013. They seem to be working well. Thanks.
Regarding the formulae,
=OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
I suppose this doesn't use COUNTA because the date has to correspond to GDP?
And,
=OFFSET(Sheet1!$K$4,0,3,1,COUNTA(Sheet1!$4:$4)-1)
Why does it require a -1?
Hi Narayank991,
Further to my queries above, say if I want to add another row (row5) on GDP growth (%), do I change the formula to
=OFFSET(Sheet1!$K$5,0,3,1,COUNTA(Sheet1!$5$5)-1)
But in this case, the NamedRange for date no longer is linked to the data via COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil) of =OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
Further to my queries above, say if I want to add another row (row5) on GDP growth (%), do I change the formula to
=OFFSET(Sheet1!$K$5,0,3,1,COUNTA(Sheet1!$5$5)-1)
But in this case, the NamedRange for date no longer is linked to the data via COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil) of =OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
NARAYANK991
Excel Ninja
Hi ,
The dynamic named range which is more important is the data range , since your X-axis quarters , in row 2 , have already been filled in till 1Q2014 ; you can even have the row filled in for another 5 years , if you wish.
So ,we should extend the X-axis as far as the data , and not the other way around.
So , the named range for the X-axis ( DatebyQuarter ) , uses the formula :
=OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
which says that we start from $K$2 , offset this by 3 columns so that the X-axis actually starts from N2. The number of columns in the X-axis is just the number of columns in the named range for the data i.e. Real_GDP_by_Expenditure_in_SGD_mil.
The data named range has the formula :
=OFFSET(Sheet1!$K$4,0,3,1,COUNTA(Sheet1!$4:$4)-1)
which says we start by offsetting the base reference of K4 by 3 columns , to start from N4. How many columns do we go up to ? We take the result of the COUNTA function over row 4 ; this returns the number of non-blank columns in this row , and since we have data in columns N through AF , and also in column K , we need to subtract 1 ; instead of the COUNTA function , if you use the COUNT function , which counts only numeric data , then you need not subtract 1 , since the COUNT function will not count the text in column K.
Narayan
The dynamic named range which is more important is the data range , since your X-axis quarters , in row 2 , have already been filled in till 1Q2014 ; you can even have the row filled in for another 5 years , if you wish.
So ,we should extend the X-axis as far as the data , and not the other way around.
So , the named range for the X-axis ( DatebyQuarter ) , uses the formula :
=OFFSET(Sheet1!$K$2,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
which says that we start from $K$2 , offset this by 3 columns so that the X-axis actually starts from N2. The number of columns in the X-axis is just the number of columns in the named range for the data i.e. Real_GDP_by_Expenditure_in_SGD_mil.
The data named range has the formula :
=OFFSET(Sheet1!$K$4,0,3,1,COUNTA(Sheet1!$4:$4)-1)
which says we start by offsetting the base reference of K4 by 3 columns , to start from N4. How many columns do we go up to ? We take the result of the COUNTA function over row 4 ; this returns the number of non-blank columns in this row , and since we have data in columns N through AF , and also in column K , we need to subtract 1 ; instead of the COUNTA function , if you use the COUNT function , which counts only numeric data , then you need not subtract 1 , since the COUNT function will not count the text in column K.
Narayan
NARAYANK991
Excel Ninja
Hi ,
For your query , I suggest that as you add more series to your chart , let all of them be referenced to one base series ; thus for your second series , you can use the same formula for the named range :
=OFFSET(Sheet1!$K$5,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
so that any time you modify the definition of one named range , all others will adjust themselves.
Narayan
For your query , I suggest that as you add more series to your chart , let all of them be referenced to one base series ; thus for your second series , you can use the same formula for the named range :
=OFFSET(Sheet1!$K$5,0,3,1,COLUMNS(Real_GDP_by_Expenditure_in_SGD_mil))
so that any time you modify the definition of one named range , all others will adjust themselves.
Narayan
Hi Narayank991,
First of all, thanks a lot for your help.
From what I can gather, it means that it is better to reference my data to one single NamedRange, in this case do you agree that choosing the Date as the NamedRange is a good idea?
For example,
=OFFSET(Sheet1!$K$4,0,3,1,COLUMNS(DateByQuarter))
NARAYANK991
Excel Ninja
Hi ,
The point is that you have already filled in the dates beyond your data ; so if you reference all your named ranges to your X-axis , the X-axis will have all the quarters , and where there is no data , you will have a value of 0 ; if this is acceptable to you , no problem.
Referencing all the named ranges to any one data row will ensure that the X-axis , and all the data series will extend to as many columns as the referenced data row.
It's your choice really , but my suggestion is only that instead of referencing each named range on its own , reference all of them to one named range , which ever one it is ; that way when the base named range changes , all the others will follow suit.
Narayan
The point is that you have already filled in the dates beyond your data ; so if you reference all your named ranges to your X-axis , the X-axis will have all the quarters , and where there is no data , you will have a value of 0 ; if this is acceptable to you , no problem.
Referencing all the named ranges to any one data row will ensure that the X-axis , and all the data series will extend to as many columns as the referenced data row.
It's your choice really , but my suggestion is only that instead of referencing each named range on its own , reference all of them to one named range , which ever one it is ; that way when the base named range changes , all the others will follow suit.
Narayan
NARAYANK991
Excel Ninja
Hi ,
This is a totally new twist ; are there any more to come ?
First , I do not understand why the column labelled Last Update has to be in the midst of data , thus forcing it to move along with the data ; why can you not position it in column D ? In case you do not want to view it , why not put it out of the way in column CA or somewhere far away from the data ?
Secondly , since your data extends up to different periods , we cannot tie the X-axis to the data ; it will have to be the other way around.
I have changed the definitions of the named ranges ; see your file now.
Narayan
This is a totally new twist ; are there any more to come ?
First , I do not understand why the column labelled Last Update has to be in the midst of data , thus forcing it to move along with the data ; why can you not position it in column D ? In case you do not want to view it , why not put it out of the way in column CA or somewhere far away from the data ?
Secondly , since your data extends up to different periods , we cannot tie the X-axis to the data ; it will have to be the other way around.
I have changed the definitions of the named ranges ; see your file now.
Narayan