Solved

Excel Graphs

Posted on 2011-03-03
14
965 Views
Last Modified: 2012-05-11
Hi Sir/Madam,

Can someone please help me with dynamic charting and here are the requirements.

I am required to develop a similar graph as per the screen shot in the attached word document.
X-axis will be dates

Y-axis will be Rank in the reverse order. (High ranks will show lower in the graph and low ranks will show on the top on the Y-axis.)

The keywords to be in a drop down menu where by I can select the keyword and the respective graph for the particular keyword is then displayed. I want to have the graph on the separate tab with sample data on another tab.

The sample data to develop the graph is available in the attached excel.

Thanks!
Amita

 sample-data-new.xlsx dynamic-charting-sample-graph-ou.docx
0
Comment
Question by:deoyagya
  • 6
  • 4
  • 4
14 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35031014
Hello,

see attached. You need to create range name with the series names and use that in the data validation. The chart range also uses a range name, which is dynamically created with an Offset formula based on the value selected in the data validation list.

See attached

cheers, teylyn

chart.xlsx
0
 
LVL 5

Expert Comment

by:wellous
ID: 35031112
Hi Amita,

Pls send a photo sample (jpg,gif,bmp ...etc) instead of the docx which i can't open it.
Because i couldn't understand how it should looks.

Brgds
Wellous
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35031952
@Wellous, the Word docx opens fine. Maybe you have an Office version earlier than 2007 and need to install the free compatibility pack?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35032083
You need to create a pivot table to sum keywords by date. Then you can create a pivot chart.

cheers, teylyn


Copy-of-sample-data-new.xlsx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 350 total points
ID: 35032142
With a different data layout of the source data, you can create a better pivot chart. See attached. Select the keyword in the dropdown at the top and select the Search engine in the legend dropdown.


Copy-of-sample-data-new.xlsx
0
 

Author Comment

by:deoyagya
ID: 35033810
Wellous,

I am attaching the screen grab of the sample graph. I am looking for the similar thing done where user can anytime change the parameters using the drop down. EVen the legends can be changed using the drop down (Green line, Blue line etc).

Thanks,
Amita
 sample excel graph
0
 

Author Comment

by:deoyagya
ID: 35033818
teylyn - Thanks for taking time to complete this for me. However, the format of the excel is different than what I have specified in the sample graph. I am not very good at excel, therefore I am looking for the solution that is easy customizable for the varying requirements.


As stated in recent comments, the graph parameters can be changed using the drop down. Please let me know if this is something you can help me achieve.

Thanks,
Amita
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35033841
In the last file I attached, you can select a keyword from a drop-down and a search engine from another drop-down. This is only possible with the source data arranged as in that file.

If the source data is arranged like in your original file, then you will not achieve these charting results.

Charts rely very much on the layout of the source data. Some things just can't be done if the source data is not arranged in a certain way.

I've given you a working solution for your question. Maybe you need to follow up with another question on how to convert your original source data to the layout required for the Pivot Chart.

cheers, teylyn
0
 
LVL 5

Assisted Solution

by:wellous
wellous earned 150 total points
ID: 35039536
Hi Amita,

I am attaching my work now to check with you if i am working in the same way you want or you need different stuff?
Cheers ,
Wellous
sample-4-Amita.xls
0
 
LVL 5

Expert Comment

by:wellous
ID: 35039545
@teylyn , thank you
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35041307
Amita,

I don't understand this:

>> teylyn - Thanks for taking time to complete this for me. However, the format of the excel is different than what I have specified in the sample graph. I am not very good at excel, therefore I am looking for the solution that is easy customizable for the varying requirements.

The sample graph you have attached has a completely different data set, so it's not easy to guess what you need from your current data set. Assuming that you want to be able to chart one or more keywords for one or more search engine and let the user pick any combination of keyword(s) and Search engine(s), that is what I have provided.

Please let me know what is not working for you.

cheers, teylyn
0
 

Author Comment

by:deoyagya
ID: 35043570
Hi Teylyn,

I have sent the sample graph to my boss. As soon as I get an OK... I will be able to award the points to you. Sorry for the delay :(


Thanks,
Amita
0
 

Author Comment

by:deoyagya
ID: 35043915
Hi Wellous,

Can you give some instructions to reproduce the graph as you have created. I am unable to reproduce with the exact results.

Thanks,
Amita
0
 
LVL 5

Expert Comment

by:wellous
ID: 35044683
Hi Amita,
Simplly i modified your table a little bit to meet what i could guess from your needs,
To work in the same way ,maybe this example will make you understand my idea..

* whenever you add a new row in the table ( new date, data(Ranks) or even new key ,,,etc) it will appear in yr chart as it is..

Pls check the attachement,
Good luck & apna dhayan rakhay ;)
Brgds,
Wellous


sample-4-Amita.xls
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vlookup for in-between dates 4 38
MS excel check if Links exist 5 35
Excel Conditional Median Problem 1 15
Delete all empty columns using VBA 7 42
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question