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!!!
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
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!!!
What exactly do you not understand?
I have given you the formula to put in your example workbook in order to create chartable data.
I am not as advanced and can't get it to work. I've attached my attempts.
Thanks again,
Regan
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
Yes, this is much better, but the dates aren't the same as the real dates?
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.
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
See attached. Right click on the body of the chart and select data to see how it was set up.
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?
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks