Auto Plots Points

[tgalx3]

Hello,

I am trying to build employee hours calculator for work. I thought it would be really cool to see if excel can take the time that a value was entered, take that value entered and plot it on a line graph.

If it is possible, could someone explain how?

Thank,
Alex

[dflak]

Welcome to the forum!

Just a word of advice, you will normally get more responses if you include a sample workbook with enough date to demonstrate the issue. If necessary, mock up what you want the answer to look like and take us through an example on how you got from the raw data to the answer. If you can provide the logic, we can provide the formula or code to make it happen.

This was an easy setup, so I did not mind setting up the workbook, but it had a surprise for me until I figured out what was happening.

The simplest way to make a chart is to select a cell in the range of data you want to plat and select Insert -> and then pick the kind of chart you want. This works about 98% of the time. A little better technique is to highlight the entire range you want to plot and then select the type of chart you want.

I did this with the data I generated for the sample. I selected a line chart and got a vertical line. See Line Chart - Date Axis. The reason for this is that Excel is grouping all the data into one day. I went out to format the axis to use something smaller and found out that the least granular time period for a line chart is whole days. Since all of my points were within the same day, I got a vertical line.

I went out and changed the Axis type from date to text and got the chart shown in Line Chart - Text Axis. This looks nice, but the times are equally spaced so the gap between 11:26 AM and 6:09 PM is the same size as the gap between 9:20 AM ad 9:53 AM.

Better still, is to select an X-Y Scatter Plot for this data. The X-Axis winds up in 24 minute increments, but you can adjust that with axis options.

[tgalx3]

Sorry for not giving an example. It is difficult when I am not sure myself.

I work for a shipping company and we have to track how many packages we run per hour. We don't have a calculator able to do that, which is why I am building one.

My excel spreadsheet takes the =NOW() function for the current time. I have to put in the current amount of boxes ran to see how many boxes/hr we are running.
I would like excel to plot this data on a graph automatically.

For example:
If I input 1000 boxes & =NOW() is 05:00 AM. Excel will plot that point on a graph.
If I input 2000 boxes & =NOW() is 06:00 AM. Excel will plot that point and then draw a line between the two.
...Repeat cycle.

Is this possible? Or do I have to manually enter the data for every point?

I hope this helps.

[dflak]

I can see an issue with using the Now() function on the spreadsheet. The now function will record the time of data entry. Also it will update to the current time every time the worksheet is recalculated for ALL rows - unless you use VBA.

However, even that does not solve the issue of posting the values at data entry time as opposed to the end time or beginning time of the count. You could count the boxes at 5 AM and 6 AM and then do the data entry at 7 AM. NOW() won't help there.

However, let's back off a step or two and look at the requirement. Maybe we can come up with something better. Can you explain the process a bit and where the counting comes in.

Would the following be practical. It there is a workstation at the box processing point, it would be ideal if the person could scan the box out. However, there could be a simple application with one big button that the person can click as soon as the box leaves his hands. This application will write to a file on a shared drive. It will give two pieces of information. The time the box was "scanned out" and the login name of the person running the application. I could also make the application even more complicated. Have the user fill in how many boxes and click the button, in case you do things in bulk.

You'll get a much more granular slice on the data. You can then use a pivot table to group the data by hour, and this would display automatically. Refresh the pivot table and you will see the day's history and also how well you are doing in the current hour. Such a database can do daily, weekly, monthly, etc. trends.

This sounds very complicated, but it is actually easy to program if you have a common drive and a workstation at the box processing place and can train people to click the button.

Yes, I am going off on a tangent. I'll wait until I hear from you on the process. Think big.