Basic Spreadsheet Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fitzwilliam Darcy - 23 Feb 2007 03:24 GMT

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?

Reply to this Message

Bernard Liengme - 23 Feb 2007 13:26 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Could some of you Excel mavens help me design a basic worksheet for
> the following:
[quoted text clipped - 15 lines]
> enough traffic to bother staying open 8 to 9p? What are our peak times/
> days/hours?

Reply to this Message

Fitzwilliam Darcy - 28 Feb 2007 00:43 GMT

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 :D

Reply to this Message

Bruce Sinclair - 28 Feb 2007 04:55 GMT

>Thank you kindly for the reply.
>
[quoted text clipped - 6 lines]
>has dubious results... likely this is because I have no idea what I am
>doing :D

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. :)

Reply to this Message