Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-13-2019, 12:10 PM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default Scatter Charts

Hi,



Is anyone familiar with creating scatter charts in excel? I have the data but it is not plotting the way I am wanting to.

I need someone to explain where data goes to get the desired outcome.

Help!

Thanks.
Reply With Quote
  #2  
Old 02-13-2019, 03:37 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 516
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

Without a sample file it's hard to give an exact answer without something to work with. What are you trying to accomplish?

Anyway, here are a few sites you can reference as aides.

http://www.andypope.info/charts.htm
https://peltiertech.com/Excel/Charts/ChartIndex.html
Reply With Quote
  #3  
Old 02-13-2019, 04:02 PM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default Scatter Charts

That is understandable for sure.

The data I am trying to portray is just a pricing comparison on various terms (month to month, 1, 2, 3 and 5 year) with 5-6 different companies.

Some of the companies don't have pricing for some of the terms, so I think I can just leave those blank. Is that correct?

I am not sure how to enter the data into a spreadsheet to get it to show on the scatter properly. I want the legend to be the 5 or 6 different companies with a scatter points referencing each of the companies and their associated pricing for each of the term.

For example, company A has pricing on a 1 & 3 year term where company B has pricing for 2 & 3 year term.

How do I enter this into the excel spreadsheet? And what columns and rows so I highlight before choosing the scatter chart.

Hoping this is all somewhat clear!

thanks
Rhonda
Reply With Quote
  #4  
Old 02-13-2019, 04:47 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 516
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

I still believe a tutorial is what you should read up on.

Here's one example...

https://www.ablebits.com/office-addi...er-plot-excel/

I can't write anything better than the tutorials on line.

If you have a sample built we can surely show you how to get the chart you desire, but ultimately you are the only way who knows what the data should be saying.
Reply With Quote
  #5  
Old 02-14-2019, 08:43 AM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default Example of Data

Thanks. I read the tutorials but I the data is still not coming out as I would like. I have attached a screenshot of what I am trying to do. It is just pricing comparison between 5 companies. Some do different pricing based on term, others just do a single term price.

Hopefully you can see what I am doing wrong.

thanks
Rhonda
Attached Images
File Type: png Scatter Chart.png (9.7 KB, 22 views)
Reply With Quote
  #6  
Old 02-14-2019, 09:29 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

Do you mean, that for companies 2, 4 and 5 the price is same for all following months?

Then in your table you must have last price for every month (i.e. for every row in table).

From this follows, what about companies 1 and 3? Did they have any prices for currently empty months (p.e. was price same as last one entered, until it was changed)? When yes, then you have to fill in table all rows for all companies, when those companies had some price.
Reply With Quote
  #7  
Old 02-19-2019, 09:52 AM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default Scatter Chart

Hi,

Sorry I thought I sent this reply to you.

No, companies 2, 4 and 5 only have 3 year pricing where others have additional term pricing.

A blank space means that they don't have applicable pricing for that term. For example, company 1 has pricing on 1, 2, 3 and 5 year term. Company 3 only has pricing on a 1 and 2 year term.

thanks
Rhonda
Reply With Quote
  #8  
Old 02-20-2019, 02:48 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

Quote:
Originally Posted by RhondaPCanada View Post
No, companies 2, 4 and 5 only have 3 year pricing where others have additional term pricing.
Then what you don't like in your graph? For every year you have a point in graph!

Quote:
Originally Posted by RhondaPCanada View Post
For example, company 1 has pricing on 1, 2, 3 and 5 year term. Company 3 only has pricing on a 1 and 2 year term.
I suspect something is fishy with terminology used here. What is year term? Does it specify certain year, or something else?
Reply With Quote
  #9  
Old 02-20-2019, 06:13 AM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default

Then what you don't like in your graph? For every year you have a point in graph!

Quote:
Originally Posted by RhondaPCanada
For example, company 1 has pricing on 1, 2, 3 and 5 year term. Company 3 only has pricing on a 1 and 2 year term.

I suspect something is fishy with terminology used here. What is year term? Does it specify certain year, or something else?
Term is just each year. So 1 means that its pricing on a 1 year term, 2, means on a year term etc. This is a monthly price on a contract term.

For example, company 1 has 4 different pricing options. They have a 1, 2, 3 and 5 year price option. $65.95. $55.95, $45.95 and $43.95. Company 2 only has pricing of $59/mth on a 3 year term only.

Hopefully that is more clear.
Reply With Quote
  #10  
Old 02-20-2019, 07:27 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

Something like this?
Attached Files
File Type: xlsx MultiSeriesScatterGraph.xlsx (17.9 KB, 4 views)
Reply With Quote
  #11  
Old 02-20-2019, 08:43 AM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default

This looks better but still have some discrepancies.

For example, company 1 should have pricing in 2015, 2016, 2017 and 2019 but I don't see a marker for them in 2017.

Also the grey point plotted on the horizontal axis right above the word "years"? It says company 3 for 2017 but in the graph there is no value there.

Also if there is no values can those not be referenced on the graph? Looking at the monthly pricing of "0" it makes it seem that company 5 has a price of zero for all the years which isn't the case.

thanks
Rhonda
Reply With Quote
  #12  
Old 02-20-2019, 11:32 PM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

Quote:
For example, company 1 should have pricing in 2015, 2016, 2017 and 2019 but I don't see a marker for them in 2017
When you look more closely at lowest dot for year 2017, you see it looks like an ellips. Really there are 2 dots - for Company1 and for Company5.

For the rest, I changed how chart table shows missing values. Now the graph ignores them instead of displaying as 0's. Look into attachment. (In previous version, e.g. for year 2018 there were actually 5 dots with zero values.)
Attached Files
File Type: xlsx MultiSeriesScatterGraph.xlsx (17.9 KB, 3 views)
Reply With Quote
  #13  
Old 02-21-2019, 10:09 AM
RhondaPCanada RhondaPCanada is offline Windows XP Office 2010
Novice
 
Join Date: Feb 2019
Posts: 9
RhondaPCanada is on a distinguished road
Default

This looks great thank you!!!

A couple of questions:

1. How do I can change "company 1" etc to actually business names?
2. Also I see there are two tabs; price table and price graph. Do I prepare the price table first?

I have to do 8 other graphs with same variables but with different pricing and I am bit overwhelmed lol.
Reply With Quote
  #14  
Old 02-21-2019, 02:04 PM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

Quote:
I see there are two tabs; price table and price graph. Do I prepare the price table first?
Yes.
You'll have a table for data entry, which is designed so, that it is easy to make various calculations. The best way for this is to try to design this table as database-like as possible.
For graph, you design a table which is easy to read for graph. And all (or most) data there are read form data entry table.

Quote:
How do I can change "company 1" etc to actually business names?
When I remember correctly (I don't have Excel available at moment), company names are on both data entry and report sheets entered manually (selected from data validation lists). It is possible to read companies into report table, but this is much more complicated. The question was about graph creating, not about calculations using hidden tables and hidden columns to get a list of companies :-)
Reply With Quote
  #15  
Old 02-22-2019, 01:17 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

I could now see what was in my previous example.

I did it as simple as possible, so companies list was entered directly into Data Validation List source (select a field/range with data validation, and then from Fata menu Data Validation).

As you did write, that you want several different graphs with same data structure, then I added some features to my example, and attached the edited workbook.

In PriceTable I added a Service column. On PriceGraph, you select a service, and pricing graph for this service is displayed.

For companies and services, a separate registry sheets are added. You register company (sheet Companies) or service (sheet Services) as 1st step. Only after that you can select it in PriceTable.

The PriceGraph data table is now fully dynamic. It supports currently the number of displayed years from 5 up to 10, and the number of companies up to 10.
Attached Files
File Type: xlsx MultiSeriesScatterGraph.xlsx (23.6 KB, 2 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
After updating linked excel charts in publisher, all charts are moved effinglife Publisher 0 05-23-2017 02:27 AM
Scatter plot with only second y axis hanvyj Excel 0 07-26-2012 03:25 AM
4 quadrant scatter chart johnsongorj Excel 1 05-31-2012 12:08 PM
scatter chart gsrikanth Excel 1 03-07-2012 05:29 AM
How to create XY Scatter chart with quadrants Jamal NUMAN Excel 0 11-21-2010 04:37 PM


All times are GMT -7. The time now is 08:04 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft