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.

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

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

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.

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

Scatter Chart.png (9.7 KB, 30 views)

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.

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

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

I suspect something is fishy with terminology used here. What is year term? Does it specify certain year, or something else?

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.

Something like this?

Attached Files

MultiSeriesScatterGraph.xlsx (17.9 KB, 10 views)

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

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

MultiSeriesScatterGraph.xlsx (17.9 KB, 9 views)

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.

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.

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 :-)

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

MultiSeriesScatterGraph.xlsx (23.6 KB, 8 views)