Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




633 posts

Ultimate Geek
+1 received by user: 49


Topic # 214593 19-May-2017 13:17
Send private message quote this post

Hi all, 

 

I'm struggling a little with excel, I am trying to plot some data on a scatter graph. Essentially I have a few months of call data with a date column and time column. What I want to do is on the X axis plot the day of the week e.g Monday - Sunday and on the Y axis I want to plot the time of the day. Any ideas, I have given it a try but the X axis seems to want to plot on a time scale jan - march rather than monday - sunday. I tried creating a column with the days of the week, and plot that instead of the date but get the similar results. 

 

 

 

Any ideas help would be greatly appreciated. 


Filter this topic showing only the reply marked as answer Create new topic

mdf

1471 posts

Uber Geek
+1 received by user: 369

Trusted
Subscriber

  Reply # 1784774 19-May-2017 14:57
Send private message quote this post

Can you post a few lines of data, please?


1186 posts

Uber Geek
+1 received by user: 107


  Reply # 1784903 19-May-2017 18:06
Send private message quote this post

I think Excel will calculate the x-axis by dividing the range by the periods it can display. So if your range is 6 months long and it can only display six periods you will see months displayed on the x-axis as the major axis marker. It will display the daily data points in chronological order, but it wont match the major axis markers which be in months.

Just guessing here, if your data table has two columns formatted as date and time, Excel should pick up the formatting. However, if you right click on the x-axis and choose 'format x-axis' the fields you may want to look at are under 'axis options' and are 'bounds' (the number of rows in your table for example), major and minor.

For example, setting the major and minor units to 1 and entering ddd D (a space before the cap D) in the number format code field will display mon 2 ,tue 3, wed 4 along the bottom (sans dates)




Software Engineer

 


 
 
 
 


321 posts

Ultimate Geek
+1 received by user: 9

Subscriber

  Reply # 1784983 20-May-2017 00:59
2 people support this post
Send private message quote this post

It depends how you generate the column with the names of the days of the week.

 

 

 

If column A contains your dates and you create column B with the names of the days, the plot results will depend on how you create column B; eg.

 

1.  Bi = Ai formatted as "dddd" (gives Sunday, Monday, etc).  

 

In this case your x-scale labels will show as 'Sunday', 'Thursday', 'Monday' ... etc or similar, but the x-value of each point plotted will be at the date number (DATEVALUE), not the day name ('Wednesday' etc).  Consequently the data will be scattered at various points along the x-axis (ie. at the date number locations), not lined up in seven columns above each day label.  The 'Sunday', 'Thursday', 'Monday' labels are the day names of the dates at the label positions.  If you formatted column B as numbers, then the labels would show as numbers such as 42421 (= date number for 21/02/2016).  Anyway, this is not what you want.

 

2.  Bi = TEXT(Ai, "dddd") then you get column B filled with text values of the type 'Sunday', 'Monday', 'Tuesday', etc, which are the day names you want.  

 

However, if you use these as the x-values in your 'scatter plot' the result will not be what you want/expect.  Basically you will end up with a 'line plot' with each point plotted 1 increment along the x-axis.  That is because a 'scatter plot' in Excel requires numerical data for both axes.  If you attempt to plot a 'scatter plot' with text values for the x-axis, you effectively get a 'line plot', even though Excel shows it as a 'scatter plot'.  You can confirm this by plotting the data as a 'line plot' and setting the line colour to 'None' (so only the points are visible).  You will see that the points are in the same positions as you get for the supposed 'scatter plot'.  The x-axis labels will show as day names but your points will not appear lined up with the labels.  Again, not what you want.

 

 

 

3. Bi =WEEKDAY(Ai,1), formatted (eg. right click, Format Cells ...) as "dddd".  The '1' in the function sets the week to start at Sunday.  

 

This is what you want. You will again get column B filled with values of 'Sunday', 'Monday', 'Tuesday', etc but the underlying values are in fact 1,2,3, ... 7; ie. they are the day numbers in the week corresponding to your dates.  They show as 'Sunday', 'Monday', 'Tuesday', etc due to the format applied.  That does not change the underlying values - they are still numbers.  So now you will get your data plotted at position 1,2, or 3 etc (up to 7) but x-axis will be labelled 'Sunday', 'Monday', 'Tuesday', etc in accordance with the format applied to the data in column B.

 

A couple of additional points.  If you want only 'Sunday' - 'Saturday' displayed along the x-axis labels you will likely need to format the x-axis to have a Minimum Value of 1, and Maximum Value of 7.  Otherwise you may (will?) get 'Saturday' (day 0) as the first label, and 'Monday' as the last one (day 8). Those days will have no data above them though.

 

Plotting this way with a large amount of data will result is many points overlapping or overlaid (eg. two points where the time is the same, or close to the same).  In order to view of the density of calls vs time, you would need to apply transparency to the plotted points.

 

 

 

See example plot below: 70% transparency applied to marker Fill, 'No line' for marker Border.  Depending on how many points you have it may be better to apply a higher level of transparency.  Also had to apply a custom format to the Time scale.  

 

Not certain whether/how can get top time label to display as 24:00?  'Best' I could do is set Maximum to 0.9999, which gives the top label as 23:59 but all the other labels are also one minute short of the hour.  Not tidy!  Better as below.

 

Note: these are dates and times of a couple of hundred earthquakes so not expecting any particular pattern here!

 

 

 

 

 

 

 

 

 




633 posts

Ultimate Geek
+1 received by user: 49


Reply # 1785805 22-May-2017 09:57
Send private message quote this post

This is awesome thanks everyone for all the help. DS248 you nailed it! thank you for your extensive knowledge and expertly written response, couldn't have asked for more really! 10 minutes after reading your post I had an awesome looking scatter, just what I was after. Even down to the transparency, this makes it look so good!


Filter this topic showing only the reply marked as answer Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Nothing nebulous about Microsoft’s cloud-transition
Posted 21-Jul-2017 15:34


We’re spending more on tech, but not as much as Australians
Posted 21-Jul-2017 11:43


Endace announces EndaceFabric for network-wide packet recording
Posted 20-Jul-2017 20:49


Acorn 6: MacOS image editing for the rest of us
Posted 20-Jul-2017 17:04


HTC faces backlash over keyboard pop-up ads
Posted 19-Jul-2017 15:53


BNZ adds Visa credit cards to Android Pay wallet
Posted 18-Jul-2017 19:44


Still living in a Notification hell – Om Malik
Posted 18-Jul-2017 13:00


Duet Display uses iPad to extend Mac, PC
Posted 18-Jul-2017 10:58


PC sales could be worse
Posted 17-Jul-2017 07:34


Crypto-currencies, tulips, market bubbles
Posted 17-Jul-2017 06:38


NZ Tech Podcast: Big batteries, solar cars, cold war, IoT
Posted 16-Jul-2017 16:53


Vodafone Australia mulls Wisp alliance, NZ implications
Posted 13-Jul-2017 16:49


Rural health professionals see fibre pay-off
Posted 13-Jul-2017 11:52


Vodafone announces expansion of $5 Daily Roaming
Posted 13-Jul-2017 10:20


Intel unveils powerful Intel Xeon Scalable processors
Posted 12-Jul-2017 20:41



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.