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.


Shoes2468

785 posts

Ultimate Geek


#214593 19-May-2017 13:17
Send private message

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

mdf
3512 posts

Uber Geek

Trusted

  #1784774 19-May-2017 14:57
Send private message

Can you post a few lines of data, please?




TwoSeven
1623 posts

Uber Geek

Subscriber

  #1784903 19-May-2017 18:06
Send private message

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
   (the practice of real science, engineering and management)
A.I.  (Automation rebranded)
Gender Neutral
   (a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

 

 ...they/their/them...


DS248
1691 posts

Uber Geek

Lifetime subscriber

  #1784983 20-May-2017 00:59
Send private message

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!

 

 

 

 

 

 

 

 

 




Shoes2468

785 posts

Ultimate Geek


#1785805 22-May-2017 09:57
Send private message

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





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









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.