+ Reply to Thread
Results 1 to 13 of 13

chart the amount of emails sent each day

  1. #1
    Registered User
    Join Date
    12-09-2010
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    7

    Question chart the amount of emails sent each day

    Iíve been reading the posts on this forum for a few days and havenít found anything that solves my problem.
    I need to chart the amount of emails sent each day. I have a list of the date and time the email was sent. I see it with the Time in the Y-axis and the date on the x-axis. Each email sent would be represented as a dot in the appropriate date/time intersection. I included a picture of what I thought the chart might look like in the attachment if my description is confusing.

    Help!!!
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,120

    Re: chart the amount of emails sent each day

    You need to break the date and time information into 2 columns.

    B1: =
    C1: =EMails
    B2: =INT(A2)
    C2: =MOD(A2,1)

    copy down for data set and plot range B1:C4602 as xy-scatter
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    12-09-2010
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    7

    Lightbulb Re: chart the amount of emails sent each day

    This sounds great, I'm excited to try it, but I don't understand it!!! lol, can you give me a real example in excel?

    Also, I have a list of times that represent my arrival time, so would it be possible to lay these lines or different color dots over the emails?

    Thank you so much for your reply!!!

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,120

    Re: chart the amount of emails sent each day

    What exactly do you not understand?

    I have given you the formula to put in your example workbook in order to create chartable data.

  5. #5
    Registered User
    Join Date
    12-09-2010
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: chart the amount of emails sent each day

    I am not as advanced and can't get it to work. I've attached my attempts.

    Thanks again,
    Regan

  6. #6
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: chart the amount of emails sent each day

    Is this what you are looking for; I changed the format of your date and time cells. Look at the chart tab
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Registered User
    Join Date
    12-09-2010
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: chart the amount of emails sent each day

    Yes, this is much better, but the dates aren't the same as the real dates?

  8. #8
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: chart the amount of emails sent each day

    I didn't change the dates. They are what is in your table. Perhaps you have bad data? I thought it strange that the dates were forward and not backwards. Was this data imported or manually entered? If imported, did the original data have a year in it? If not, and this was imported after 1/1/2011, then excel assumed the current year causing the issue.

  9. #9
    Registered User
    Join Date
    12-09-2010
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    7

    Thumbs down Re: chart the amount of emails sent each day

    I pasted the information from outlook into excell and I think excel was confused with the date/time format. So, I changed the format in outlook to date only and pasted into excel. Then I changed the format to time only and pasted it into excel. Then I deleted the repeated information and was left with a date column and a time column that line up with the email.

    I couldn't get my scatter chart to work, so I threw it into a pivot table. It's not what I really wanted, but it will work!

    Regan

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: chart the amount of emails sent each day

    See attached. Right click on the body of the chart and select data to see how it was set up.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-09-2010
    Location
    Houston
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: chart the amount of emails sent each day

    But I still don't know how to create the chart like you did. My chart has two lines. Can you tell me how you made the chart?

  12. #12
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: chart the amount of emails sent each day

    I did it in 2007. I will go to my XP machine and walk through for you step by step. Give me a little bit of time.

  13. #13
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: chart the amount of emails sent each day

    I highlighted the cells D1 to E4602 and clicked on the Graphing Icon selecting Scatter chart.

    This created the same chart that you are getting.

    I right clicked on the chart, selected Source Data and reversed the X and Y values so that the dates were on the X-Axis and the Times were on the Y Axis.

    I then deleted the second series variable so that I ended up with the X-Axis is D2:D4602 and the Y Axis as E2:E4602.

    Sometimes Excel makes wrong assumptions in its wizards. Often times, building charts is easier if you do it manually. You then get exactly what you want with out having to manipulate the results that the wizards give you.

    I hope this is helpful. Good Luck with your project.

    Alan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1