Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel Graphs

Posted on 2011-03-03
14
966 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 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