Bubble Chart Help - Event Cost/Date/Attendance

[MrPlow2]

Hello,

I apologise if this question has been repeated in the past - I could not find a similar thread using the search feature.

The purpose of this chart is to show the list of events I am planning country wide with their cost and the total attendance each of these events has. That leaves me with 3 variables (date), cost and attendance. This leads me to believe that a bubble chart would be the best suited excel chart for this.

The issue I am having is that I can't seem to find out how to get the date to show on the X axis .

I'm picturing a chart with the dates of a month (lets assume July) on the X axis, with the attendace ranging from 0 - 3,000 on the Y axis. What my goal is to have the define would be the total costs of the event. The information that the bubbles would imply is the exposure opportunities for each event.

Again, i'm not sure if the bubble chart would be best for this type of information but any help or direction would be appreciated.

MrPlow

[cbatrody]

Hi,

Welcome to the Forum.

I guess the bubble chart will be appropriate for this scenario. You can show the Attendance in Y Axis, Cost will be the size of the bubble and Date in X-Axis.

Please post a sample data if you need any further assistance in plotting the bubble chart.


Regards,
Chandra

[MrPlow2]

Hi,

Welcome to the Forum.

I guess the bubble chart will be appropriate for this scenario. You can show the Attendance in Y Axis, Cost will be the size of the bubble and Date in X-Axis.

Please post a sample data if you need any further assistance in plotting the bubble chart.


Regards,
Chandra

Hi Chandra,

Thank you for the reply. Here is some sample datasample data.xlsx

It must be something simple that I am doing wrong, but I can't get the Y axis to be the size of the event, the X asix to be date and the bubble to be the cost of the event.

One other issue i'm having is that I can't format the X and Y axis to be evenly spaced. If possible i'd like to learn how to format the Axis to have $500 increments on the Y axis and the X axis to be weekly intervals (52/year)

I appreciate the help,

MrPlow

[MrShorty]

Follow these instructions: http://office.microsoft.com/en-us/ex...749.aspx?CTT=1

At step 2, try with and without column headers -- I have found that Excel's default algorithms for "guessing" whether you want data in rows or columns and such is not always very good. Trying different selections may help Excel do better at guessing.

At step 3-5, if Excel has "guessed" wrong, then go into the "select data" dialog and edit, remove, add a data series to get the desired columns into the desired fields (http://office.microsoft.com/en-us/ex...980.aspx?CTT=1). If you are unfamiliar with this dialog, you will want to get familiar with this. The select data dialog is needed when Excel fails to "guess" how you want to plot the data. This dialog allows you to better instruct Excel how you want to plot the data.

(Aside -- In earlier versions of Excel (before 2003), Excel used to bring up the chart wizard in creating charts which included this select data dialog. Using the newer versions of Excel, I miss that feature.)

[MrPlow2]

Follow these instructions: http://office.microsoft.com/en-us/ex...749.aspx?CTT=1

At step 2, try with and without column headers -- I have found that Excel's default algorithms for "guessing" whether you want data in rows or columns and such is not always very good. Trying different selections may help Excel do better at guessing.

At step 3-5, if Excel has "guessed" wrong, then go into the "select data" dialog and edit, remove, add a data series to get the desired columns into the desired fields (http://office.microsoft.com/en-us/ex...980.aspx?CTT=1). If you are unfamiliar with this dialog, you will want to get familiar with this. The select data dialog is needed when Excel fails to "guess" how you want to plot the data. This dialog allows you to better instruct Excel how you want to plot the data.

(Aside -- In earlier versions of Excel (before 2003), Excel used to bring up the chart wizard in creating charts which included this select data dialog. Using the newer versions of Excel, I miss that feature.)

Awesome, I feel like i'm about 99% there.

The only issue I have right now is to make the X axis a timeline for the year - the data that i'm importing into the chart (11 entries with 11 specific dates). Would I be able to change this by adjusting the major/minor units?
maj

Ideally I would like it to have 12 major units (which represent months) and either 52 (weeks) or 365 (days) as a minor unit. I'm not too sure how to accomplish this.

Thank again for all of the help. This website is a fantastic resource


Mrplow

[MrShorty]

Ideally I would like it to have 12 major units (which represent months) and either 52 (weeks) or 365 (days) as a minor unit. I'm not too sure how to accomplish this.

I would probably suggest you start with something like this (http://www.cpearson.com/Excel/datetime.htm#SerialDates) to become familiar with how Excel stores and handles dates and times.

It would be easy to set the minor unit to 1 day or 7 days -- simply put 1 or 7 in for the minor unit.

You could easily set the major unit to be 30 or 31 days, though this means that you major divisions will not be "exactly" one month (because some months are 30 days and others are 31).

I don't always like working with Excel's date/time serials, I would be tempted to add a column that would be my own "month" counter. this would just have values of 1 through 12.

A little bit more advanced option might be to use Jon Peltier's arbitrary axis technique to label the months. http://peltiertech.com/Excel/Charts/ArbitraryAxis.html