Newbie needs help data collection, naming columns, pulling in data.

[libertyforall]

I need help. I am new to Excel and I am doing some
volunteer research for the University of Hawaii. I have several
questions and will have several more. To get started here are my first
questions. I know my questions are very basic so please bare with me.
I would greatly appreciate any help. Thanks.


Roy

1. How do I name a column?
2. How can I copy the information from the link below
without manually typing in every single value? Is it possible to
pull in the whole document? I tried copying one row of data but it put
it in one cell instead of one row.

http://mkwc.ifa.hawaii.edu/archive/wx/wekiu/mk1.2007.txt

3. Once I pull in the data, how do I average one value. For
example. If I want to know the average temperature at 12:30 am during
the month of August, what command would I need to use and how would I
input it?

 

[JoeU2004]

I need help. [....] To get started here
are my first questions.

You asked the wrong first question. Your first question should be: how do
I educate myself about the basics of Excel?

Answer: Get thee to a bookstore and find the simplest book you can find.
One such book: "Excel for Dummies". Don't be put off by the title. It is
a pejorative comment about you. Caveat emptor: I have never used that
particular book myself. I just know that the "For Dummies" books tend to be
very basic and straight-forward.

1. How do I name a column?

You enter the title as text into a cell, for example a cell in the first
row.

2. How can I copy the information from the
link below without manually typing in every
single value? Is it possible to pull in the
whole document?

Yes, but usually not with satisfactory results.

First, save the data into a local ".txt" file. Open the file in Notepad and
make some prudent edits. For example, you will probably want to eliminate
the "underscores", which is really a row of dashes in your data.

Second, open an Excel worksheet, click on Data > Import External Data >
Import Data. After selecting the file, following the Import Wizard.

If that does not give you 100% results, at least it should do 90-95% of the
work. You will probably need to select some formats, and you might need to
edit some cells.

3. Once I pull in the data, how do I average one value.
For example. If I want to know the average temperature

Something like =AVERAGE(F1:F1000).


----- original message ------

 

[JoeU2004]

PS....

1. How do I name a column?

Perhaps you meant: how to I name a column of data?

Click on Insert > Name > Define, and fill in the Name and Refers To fields.


----- original message -----

I need help. [....] To get started here
are my first questions.

You asked the wrong first question. Your first question should be: how
do I educate myself about the basics of Excel?

Answer: Get thee to a bookstore and find the simplest book you can find.
One such book: "Excel for Dummies". Don't be put off by the title. It
is a pejorative comment about you. Caveat emptor: I have never used that
particular book myself. I just know that the "For Dummies" books tend to
be very basic and straight-forward.

1. How do I name a column?

You enter the title as text into a cell, for example a cell in the first
row.

2. How can I copy the information from the
link below without manually typing in every
single value? Is it possible to pull in the
whole document?

Yes, but usually not with satisfactory results.

First, save the data into a local ".txt" file. Open the file in Notepad
and make some prudent edits. For example, you will probably want to
eliminate the "underscores", which is really a row of dashes in your data.

Second, open an Excel worksheet, click on Data > Import External Data >
Import Data. After selecting the file, following the Import Wizard.

If that does not give you 100% results, at least it should do 90-95% of
the work. You will probably need to select some formats, and you might
need to edit some cells.

3. Once I pull in the data, how do I average one value.
For example. If I want to know the average temperature

Something like =AVERAGE(F1:F1000).


----- original message ------

I need help. I am new to Excel and I am doing some
volunteer research for the University of Hawaii. I have several
questions and will have several more. To get started here are my first
questions. I know my questions are very basic so please bare with me.
I would greatly appreciate any help. Thanks.


Roy

1. How do I name a column?
2. How can I copy the information from the link below
without manually typing in every single value? Is it possible to
pull in the whole document? I tried copying one row of data but it put
it in one cell instead of one row.

http://mkwc.ifa.hawaii.edu/archive/wx/wekiu/mk1.2007.txt

3. Once I pull in the data, how do I average one value. For
example. If I want to know the average temperature at 12:30 am during
the month of August, what command would I need to use and how would I
input it?

 

[Per Jessen]

Hi Roy

1. Not sure what you want to do, but maybe this: You can have headings in
first row(s). You can not rename column labels (A, B, C etc.)

2. Open the web site and select/copy all date on the page. Open Excel, right
click on cell A1 > Paste. Now all data are in column 1. To distribute data
to next columns, goto Data > TextToColumns > Delimited > Next > Delemiters:
Check "Space" > Finish.

Now, you have to move some headings to their proper columns.

3. First we need to use a helper column to calculate the month from the date
in column A.
In S4 enter this formula:
=Month(A4)

In T1:V1 enter theese headings: "Month", "Time", "AVG"
In T2 enter the month number for the desired month, in U2 enter the desired
time.
In U2 Enter this formula (one line):

=SUMPRODUCT(--(S4:S50000=T2),--(B4:B50000=U2),D450000)/(SUMPRODUCT(--(S4:S50000=T2),--(B4:B50000=U2)))

If you have more than 50000 rows of data, you have to change the number in
the formula.

Hopes this helps.