Basic Spreadsheet Help

[Fitzwilliam Darcy]

Could some of you Excel mavens help me design a basic worksheet for
the following:

On a piece of paper, Monday through Thursday we record how many people
come into the lab from 5-9 p.m. We make an x for each person coming in
and tally it at the end of an hour. So our worksheet looks something
like this for each day M-Th:

Monday 2/19/07
5-6p xxxxxxxxxx (Hourly Total 10)
6-7p xxxxx (Hourly Total 5)
7-8p xxxxxx (Hourly Total 6)
8-9p xxxx (Hourly Total 4)
TOTAL 25 Visitors

Can I get some suggestions on how to set this up in Excel so that we
can use the data? Some questions we want to answer are: 1. Do we have
enough traffic to bother staying open 8 to 9p? What are our peak times/
days/hours?

 

[Bernard Liengme]

In B2:B3 enter 2/1/2007 and 2/2/2007
Select B2:B3 and drag across to B29 giving dates for Feb
In A2:A3 enter 5:50 and 6:30
Select A2:A3 and drag down as far as needed to get times of the day
From your paper worksheets, enter the data

Now use functions like SUM(), Average(), Min() and Max)
Try making a Line chart of the data

all the best

 

[Fitzwilliam Darcy]

Thank you kindly for the reply.

I wasn't sure what you meant about the time, though. How should I
enter the hour interval in the spreadsheet? Our one hour interval is
always 5-6 PM, 6-7 PM, 7-8 PM, and 8-9 PM.

I have tried two columns and even tried 18:00 - 19:00 (which didn't
seem to work at all). When I try to do charts based on the data, it
has dubious results... likely this is because I have no idea what I am
doing

 

[Bruce Sinclair]

Thank you kindly for the reply.

I wasn't sure what you meant about the time, though. How should I
enter the hour interval in the spreadsheet? Our one hour interval is
always 5-6 PM, 6-7 PM, 7-8 PM, and 8-9 PM.

I have tried two columns and even tried 18:00 - 19:00 (which didn't
seem to work at all). When I try to do charts based on the data, it
has dubious results... likely this is because I have no idea what I am
doing

Remember that dates and times are just numbers to XL. How you display them
(with formatting) is irrelevant to the underlying number.It's not clear from
what you typed above what you want to achieve.
Beware charts also if you have x values that are not evenly spaced (eg 5,
10, 50, 200). IIRC all charts in XL (except scatter) even out the x axis
spacing. In this case, 5, 10, 50 and 200 would be evenly spaced. Straight
lines can look like pretty curves - a real trap for young players.