• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic charts

Hi Sir, Thanks,
It means, for Months15 we have to select a pre-determined named ranges as in our case ='Data sheet'!$B$28:$B$39 to avoid to add every month and it covers Year 2015 full months. when we will add the date it will automatically dynamically change the chart.

Sir, this graph will fine to comparison to show three financial year
 
You have to add new series to the chart manually
So it is just as easy to set it up once
then it is ok for 12 months
 
Hi Sir,

Now on the same data.
I want to show comparison in quarters for financial years
like Quarter1 year 13 , Aprl13,May 13, June13 with Year 2014 and Year 2015 April 2014, May 2014, June 2014 and April 2015, May 2015 M June 2015 and next quarters with financial years.

So in this case I have to make dynamic ranges as I have maked for months like Months13, Months 14, Months 15. similarly Quarter1 Quartr 2, Quarter 3, Quarter 4 Average.

So I have to add another columns for average which add Quarter 3 months average. and further so on.


quarter on Quarter
Month on month
fy 13 fy 14
apr 8 apr 10
may 9 may 9
jun 10 jun 8
Quarter 1 9% Quater 1 9%
 
Hi Sir,

I tried for it, I made Quart1, Quarter2, Quarter3 and Quarter4 and divided them into according to financial year. but i also want their average should show besides these no.

upload_2015-4-2_12-27-0.png
above is one of the draft which i am thinking to make which will also dynamically update months their nos but also i want to show their average of that quarter and show this quarter fy 2014 was this was last three months no and their average was this with the comparison with other months and financial year
 

Attachments

  • hui graph 3rd.xlsx
    27.3 KB · Views: 0
Do you want what you have or
Each quarter on the X Axis Q1-4
Then 3 lines for each year

That way you can see the qtr v qtr comparison and yearly trends
 
hi sir I think that's best I have no idea about it but I want dynamic with quarter to quarter comparison with yrly comparison as u r saying if possible to show average quarterly and yearly
 
Thanks Sir for telling me basic principle of a forum. From next time I will keep these things in mind. I was involved so much that i forget that other person time is precious as our. I will keep patience.
 
Hi Sir,

Request to you please guide me in plotting the graph with Quarter wise and yearly trends Data.
Thanks in Advance
 
How about this
These are quarterly averages

upload_2015-4-5_22-10-55.png
see attached file:
 

Attachments

  • hui graph 3rd-1.xlsx
    29.4 KB · Views: 0
Hi Sir,
Thanks for the solution but
I have some query I tried it but unable to grasp its concept.

in this formula '=MATCH($C$2,'Data sheet'!$C$3:$Y$3,0),
Sir, instead of C$3:$Y$3,0 can i put Data_Set, because its shrink or expand according to columns.and Data_Set is our dynamic ranged.

Second thing
Please explain me the concept of the below formula.I know its average formula with average and IFERROR, but i want to understand its concept.

=IFERROR(AVERAGE(OFFSET('Data sheet'!$B$3,(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2,$C$3,3,1)),0)

In Data Sheet we select the range $B$3, What I understood this is the range from our data we start, if we have the data for the month of jan'13 then it will start from $C$4 or else.

(ROWS($B$5:$B5)-1)*12 why we are taking -1 and 12 why not other numbers like -2 or *24. can you explain me how does rows works.

+COLUMNS($C$4:C$4)*3-2,$C$3,3,1)
+ means the rows out come plus columns out put.
we are doing multiply by 3 because we are taking every quarter average
but why we are doing -2 and end we have taken 3,1 .

Sir, I would request you to please help me understanding the concept of this formula. How does offset works with rows and columns.
 
Can you use Data_Set?
Yes

=IFERROR(AVERAGE(OFFSET('Data sheet'!$B$3,(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2,$C$3,3,1)),0)

Iferror() just returns a 0 if there is an error, which may happen if you copy the formula down too far

So the formula is actually

=AVERAGE(OFFSET('Data sheet'!$B$3,(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2,$C$3,3,1))

So this is just an average of the offset function

=AVERAGE(OFFSET('Data sheet'!$B$3,(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2,$C$3,3,1))

The offset Function gets a 3 Row by 1 Column array using the last two parameters
OFFSET('Data sheet'!$B$3,(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2,$C$3,3,1)


So the rest of the Offset function tells it where to get it from
OFFSET('Data sheet'!$B$3,(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2,$C$3,3,1)

'Data sheet'!$B$3 is a reference point on the Data Sheet just above the Tqble
It is fixed

(ROWS($B$5:$B6)-1)*12+COLUMNS($C$4:C$4)*3-2 is a formula that looks at the number of Rows from $B$5 to $B6 and multiplies it by 12
That is as the formula is copied down it adds 12 months to the offset

COLUMNS($C$4:C$4)*3 is a formula that looks at the number of Columns from $C$4 to C$4 and multiplies it by 3
That is as the formula is copied across adds 3 months (a quater) to the offset
The 2 is just to allow for the fact that the reference point is in Row 3


 
Sir thanks for the reply before that sir I have a issue;I am able to select data in chart my chart heading is changing according the drop down but series are not moving . I am facing this issue again and again please help me what'the method if chanig the series in chart according to drop down.how we connect them.
 
The Quarterly chart won't move as it is a summation of all the data
If you want it summated per some other field you never said ?
 
Good Morning Sir, Actually Sir, I was talking previous discussion as I was doing it in office then I was facing problem in linkage data with chart. Like chart title was moving but data series was not moving. how to plot the chart. attached is the sheet.
Sir, there is also a request, later rectified from my side that my Financial start from April, So I was showing comparison from Aprl2013_14 April2014_15, April2015_16. Our Data Starts from Apirl 13,

Instead of Jan to Dec in months I wan to show them from April to Marh

upload_2015-4-11_8-21-32.png

Sir, I am able to make data series but unable to move data series with selection of drop down.

Where I am making mistake. attached is the file with some changes.
Thanks Sir
 

Attachments

  • NEW ONE.xlsx
    21.1 KB · Views: 0
Hi Sir, Thanks for the reply,request to you see my attached sheet where I have made third graph. I am able to add the data series but where i am not able to link with chart with data.
What are the steps
like I make all the data ranges.
then select chart
then make data series into it.
selected x axis level and make then into months ctrl+1 then custom mmm
then I selected chart title with shift2 and then enter.

but chart is not updating on drop down or do not change with drop down,


Thanks
 

Attachments

  • NEW ONE.xlsx
    21.1 KB · Views: 1
Thats what I did on the bottom chart of my last post !

Did you look at it ?

Look at the Data Series and then look at the Named Formula that are associated with each series
 
Hi Sir,

When I am selecting the drop down to selecting the criteria in % like total attrition % or OA Attrition w/o Inductin lke criteria then graph is showing zero in all quarters and when I am changing it in %,then no was showing in %.

Request you to please solve the problem.
 
Select the yellow table
Increase the decimals by 1 or 2 decimal places
 
You must log in or register to reply here.
Back
Top