Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

They are stored on paper...

"Justin" wrote:

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

So much for the 'painless' way of getting the information into Excel. It
might be possible to use some OCR tools and scan them into .txt files to be
read in, maybe not, but even so, it might take some code to read and separate
the data the way you need. Something tells me someone is going to be doing a
lot of typing for a while - got any handy undergrads laying around hungry for
some extra credit?

Here's how I'd probably start out - I would put ALL information for ALL
results on a single sheet - assuming they'll fit within 65535 rows.

Sit down and plan this out a little before starting out. Besides the
obvious information to be entered such as student ID/Name, scores, etc. You
will want to also consider all of the ways you may want to break up the data
later for different analysis. So even though maybe the fact that a series of
results were taken by 5th graders is only implied by the pile they are in, if
you plan on analyzing by education level later, then you'll want to
explicitly have an education level column for each result. Same for
ethnicity, native language, etc. Consider other things you may not think you
need now, but may later, such as date of test.

What you want to do initially is be painfully detailed in your data
gathering, so much so that you don't have to go back and redo even a small
part of it. Might even have a column to correlate the data on the row with
the source document for it.

Think of shortcut ways to help make things easier and more consistent. For
instance, in the columns for ethnic origin or native language, you might set
up Data Validation containing all possible groups so that the data entry
clerk can just choose from a list which will make it a little easier and
definitely make it easier for analysis later.

Having everything on a single sheet will make it easier to tell what you've
done and what you haven't and see gaps in data. It will also make it easy to
use Excel functions like VLookup to move groups of data to other sheets to
get it separated out. But Job-One is to get the data into the workbook.

Now, I'm guessing that 8 years worth of results is a whole big bunch. You
won't want to lose anything halfway through this project. Make sure you back
that file up regularly - preferably to either a removable media or at least
to another computer so that catastrophic failure of the system doesn't cost
you all of your work. Having it on removable media like a thumb drive or
flash card can even help protect against catastrophic damage at the facility.

Now once you've gotten everything into the workbook and have a plan on how
you want to look at all of the data, then we can start on that phase of it.
You can be planning some of it while data entry is in progress - but until
you know specifically what data you have to work with, it's going to be hard
to finalize that part of the game plan.

It might even turn out that this will be better suited to being processed by
a database application like MSFT Access. Having all the informatioin on a
single sheet with each student/result as a single row entry will make getting
the data from Excel into the database a classic "piece of cake".

Once you get to that stage, if you'd like to see if I have any more help to
offer - and I may or may not - feel free to get in touch with me via email at
2kmaro @ dslr.net (remove spaces) - just kind of remind me where this
conversation started.

Good luck. Aspercream is good for fingers worn out through days of endless
typing <g

"Justin" wrote:

They are stored on paper...

"Justin" wrote:

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

Thank you for that prompt and detailed reply. Well, I guess it's time to get
to work on this...I'll write again soon.

"JLatham" wrote:

So much for the 'painless' way of getting the information into Excel. It
might be possible to use some OCR tools and scan them into .txt files to be
read in, maybe not, but even so, it might take some code to read and separate
the data the way you need. Something tells me someone is going to be doing a
lot of typing for a while - got any handy undergrads laying around hungry for
some extra credit?

Here's how I'd probably start out - I would put ALL information for ALL
results on a single sheet - assuming they'll fit within 65535 rows.

Sit down and plan this out a little before starting out. Besides the
obvious information to be entered such as student ID/Name, scores, etc. You
will want to also consider all of the ways you may want to break up the data
later for different analysis. So even though maybe the fact that a series of
results were taken by 5th graders is only implied by the pile they are in, if
you plan on analyzing by education level later, then you'll want to
explicitly have an education level column for each result. Same for
ethnicity, native language, etc. Consider other things you may not think you
need now, but may later, such as date of test.

What you want to do initially is be painfully detailed in your data
gathering, so much so that you don't have to go back and redo even a small
part of it. Might even have a column to correlate the data on the row with
the source document for it.

Think of shortcut ways to help make things easier and more consistent. For
instance, in the columns for ethnic origin or native language, you might set
up Data Validation containing all possible groups so that the data entry
clerk can just choose from a list which will make it a little easier and
definitely make it easier for analysis later.

Having everything on a single sheet will make it easier to tell what you've
done and what you haven't and see gaps in data. It will also make it easy to
use Excel functions like VLookup to move groups of data to other sheets to
get it separated out. But Job-One is to get the data into the workbook.

Now, I'm guessing that 8 years worth of results is a whole big bunch. You
won't want to lose anything halfway through this project. Make sure you back
that file up regularly - preferably to either a removable media or at least
to another computer so that catastrophic failure of the system doesn't cost
you all of your work. Having it on removable media like a thumb drive or
flash card can even help protect against catastrophic damage at the facility.

Now once you've gotten everything into the workbook and have a plan on how
you want to look at all of the data, then we can start on that phase of it.
You can be planning some of it while data entry is in progress - but until
you know specifically what data you have to work with, it's going to be hard
to finalize that part of the game plan.

It might even turn out that this will be better suited to being processed by
a database application like MSFT Access. Having all the informatioin on a
single sheet with each student/result as a single row entry will make getting
the data from Excel into the database a classic "piece of cake".

Once you get to that stage, if you'd like to see if I have any more help to
offer - and I may or may not - feel free to get in touch with me via email at
2kmaro @ dslr.net (remove spaces) - just kind of remind me where this
conversation started.

Good luck. Aspercream is good for fingers worn out through days of endless
typing <g

"Justin" wrote:

They are stored on paper...

"Justin" wrote:

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

Hi again:

What exactly did you mean by this sentence, "Might even have a column to
correlate the data on the row with
the source document for it."? I am working on setting up the columns now
and am trying to figure out how to make everything fit on the page. Where
would I put the ranges needed for analyzing frequency, for example?


"JLatham" wrote:

So much for the 'painless' way of getting the information into Excel. It
might be possible to use some OCR tools and scan them into .txt files to be
read in, maybe not, but even so, it might take some code to read and separate
the data the way you need. Something tells me someone is going to be doing a
lot of typing for a while - got any handy undergrads laying around hungry for
some extra credit?

Here's how I'd probably start out - I would put ALL information for ALL
results on a single sheet - assuming they'll fit within 65535 rows.

Sit down and plan this out a little before starting out. Besides the
obvious information to be entered such as student ID/Name, scores, etc. You
will want to also consider all of the ways you may want to break up the data
later for different analysis. So even though maybe the fact that a series of
results were taken by 5th graders is only implied by the pile they are in, if
you plan on analyzing by education level later, then you'll want to
explicitly have an education level column for each result. Same for
ethnicity, native language, etc. Consider other things you may not think you
need now, but may later, such as date of test.

What you want to do initially is be painfully detailed in your data
gathering, so much so that you don't have to go back and redo even a small
part of it. Might even have a column to correlate the data on the row with
the source document for it.

Think of shortcut ways to help make things easier and more consistent. For
instance, in the columns for ethnic origin or native language, you might set
up Data Validation containing all possible groups so that the data entry
clerk can just choose from a list which will make it a little easier and
definitely make it easier for analysis later.

Having everything on a single sheet will make it easier to tell what you've
done and what you haven't and see gaps in data. It will also make it easy to
use Excel functions like VLookup to move groups of data to other sheets to
get it separated out. But Job-One is to get the data into the workbook.

Now, I'm guessing that 8 years worth of results is a whole big bunch. You
won't want to lose anything halfway through this project. Make sure you back
that file up regularly - preferably to either a removable media or at least
to another computer so that catastrophic failure of the system doesn't cost
you all of your work. Having it on removable media like a thumb drive or
flash card can even help protect against catastrophic damage at the facility.

Now once you've gotten everything into the workbook and have a plan on how
you want to look at all of the data, then we can start on that phase of it.
You can be planning some of it while data entry is in progress - but until
you know specifically what data you have to work with, it's going to be hard
to finalize that part of the game plan.

It might even turn out that this will be better suited to being processed by
a database application like MSFT Access. Having all the informatioin on a
single sheet with each student/result as a single row entry will make getting
the data from Excel into the database a classic "piece of cake".

Once you get to that stage, if you'd like to see if I have any more help to
offer - and I may or may not - feel free to get in touch with me via email at
2kmaro @ dslr.net (remove spaces) - just kind of remind me where this
conversation started.

Good luck. Aspercream is good for fingers worn out through days of endless
typing <g

"Justin" wrote:

They are stored on paper...

"Justin" wrote:

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

Do ethnicity and language need to have a number assigned to each option or
should I just use the text when creating validation?

"Justin" wrote:

Hi again:

What exactly did you mean by this sentence, "Might even have a column to
correlate the data on the row with
the source document for it."? I am working on setting up the columns now
and am trying to figure out how to make everything fit on the page. Where
would I put the ranges needed for analyzing frequency, for example?


"JLatham" wrote:

So much for the 'painless' way of getting the information into Excel. It
might be possible to use some OCR tools and scan them into .txt files to be
read in, maybe not, but even so, it might take some code to read and separate
the data the way you need. Something tells me someone is going to be doing a
lot of typing for a while - got any handy undergrads laying around hungry for
some extra credit?

Here's how I'd probably start out - I would put ALL information for ALL
results on a single sheet - assuming they'll fit within 65535 rows.

Sit down and plan this out a little before starting out. Besides the
obvious information to be entered such as student ID/Name, scores, etc. You
will want to also consider all of the ways you may want to break up the data
later for different analysis. So even though maybe the fact that a series of
results were taken by 5th graders is only implied by the pile they are in, if
you plan on analyzing by education level later, then you'll want to
explicitly have an education level column for each result. Same for
ethnicity, native language, etc. Consider other things you may not think you
need now, but may later, such as date of test.

What you want to do initially is be painfully detailed in your data
gathering, so much so that you don't have to go back and redo even a small
part of it. Might even have a column to correlate the data on the row with
the source document for it.

Think of shortcut ways to help make things easier and more consistent. For
instance, in the columns for ethnic origin or native language, you might set
up Data Validation containing all possible groups so that the data entry
clerk can just choose from a list which will make it a little easier and
definitely make it easier for analysis later.

Having everything on a single sheet will make it easier to tell what you've
done and what you haven't and see gaps in data. It will also make it easy to
use Excel functions like VLookup to move groups of data to other sheets to
get it separated out. But Job-One is to get the data into the workbook.

Now, I'm guessing that 8 years worth of results is a whole big bunch. You
won't want to lose anything halfway through this project. Make sure you back
that file up regularly - preferably to either a removable media or at least
to another computer so that catastrophic failure of the system doesn't cost
you all of your work. Having it on removable media like a thumb drive or
flash card can even help protect against catastrophic damage at the facility.

Now once you've gotten everything into the workbook and have a plan on how
you want to look at all of the data, then we can start on that phase of it.
You can be planning some of it while data entry is in progress - but until
you know specifically what data you have to work with, it's going to be hard
to finalize that part of the game plan.

It might even turn out that this will be better suited to being processed by
a database application like MSFT Access. Having all the informatioin on a
single sheet with each student/result as a single row entry will make getting
the data from Excel into the database a classic "piece of cake".

Once you get to that stage, if you'd like to see if I have any more help to
offer - and I may or may not - feel free to get in touch with me via email at
2kmaro @ dslr.net (remove spaces) - just kind of remind me where this
conversation started.

Good luck. Aspercream is good for fingers worn out through days of endless
typing <g

"Justin" wrote:

They are stored on paper...

"Justin" wrote:

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.

Answers coming via email !

"Justin" wrote:

Do ethnicity and language need to have a number assigned to each option or
should I just use the text when creating validation?

"Justin" wrote:

Hi again:

What exactly did you mean by this sentence, "Might even have a column to
correlate the data on the row with
the source document for it."? I am working on setting up the columns now
and am trying to figure out how to make everything fit on the page. Where
would I put the ranges needed for analyzing frequency, for example?


"JLatham" wrote:

So much for the 'painless' way of getting the information into Excel. It
might be possible to use some OCR tools and scan them into .txt files to be
read in, maybe not, but even so, it might take some code to read and separate
the data the way you need. Something tells me someone is going to be doing a
lot of typing for a while - got any handy undergrads laying around hungry for
some extra credit?

Here's how I'd probably start out - I would put ALL information for ALL
results on a single sheet - assuming they'll fit within 65535 rows.

Sit down and plan this out a little before starting out. Besides the
obvious information to be entered such as student ID/Name, scores, etc. You
will want to also consider all of the ways you may want to break up the data
later for different analysis. So even though maybe the fact that a series of
results were taken by 5th graders is only implied by the pile they are in, if
you plan on analyzing by education level later, then you'll want to
explicitly have an education level column for each result. Same for
ethnicity, native language, etc. Consider other things you may not think you
need now, but may later, such as date of test.

What you want to do initially is be painfully detailed in your data
gathering, so much so that you don't have to go back and redo even a small
part of it. Might even have a column to correlate the data on the row with
the source document for it.

Think of shortcut ways to help make things easier and more consistent. For
instance, in the columns for ethnic origin or native language, you might set
up Data Validation containing all possible groups so that the data entry
clerk can just choose from a list which will make it a little easier and
definitely make it easier for analysis later.

Having everything on a single sheet will make it easier to tell what you've
done and what you haven't and see gaps in data. It will also make it easy to
use Excel functions like VLookup to move groups of data to other sheets to
get it separated out. But Job-One is to get the data into the workbook.

Now, I'm guessing that 8 years worth of results is a whole big bunch. You
won't want to lose anything halfway through this project. Make sure you back
that file up regularly - preferably to either a removable media or at least
to another computer so that catastrophic failure of the system doesn't cost
you all of your work. Having it on removable media like a thumb drive or
flash card can even help protect against catastrophic damage at the facility.

Now once you've gotten everything into the workbook and have a plan on how
you want to look at all of the data, then we can start on that phase of it.
You can be planning some of it while data entry is in progress - but until
you know specifically what data you have to work with, it's going to be hard
to finalize that part of the game plan.

It might even turn out that this will be better suited to being processed by
a database application like MSFT Access. Having all the informatioin on a
single sheet with each student/result as a single row entry will make getting
the data from Excel into the database a classic "piece of cake".

Once you get to that stage, if you'd like to see if I have any more help to
offer - and I may or may not - feel free to get in touch with me via email at
2kmaro @ dslr.net (remove spaces) - just kind of remind me where this
conversation started.

Good luck. Aspercream is good for fingers worn out through days of endless
typing <g

"Justin" wrote:

They are stored on paper...

"Justin" wrote:

Thank you for answering. I will have to ask how those scores are stored now,
whether it is electronically or on paper. I had started out by creating a
separate sheet for each grade. Would that work as well? The specific
characteristics would be ethnicity, native language, and so on.

"JLatham" wrote:

Justin,
This one just needs more information. How are the scores saved right now?
Electronically somewhere or just on paper? If they are in electronic files,
do you know what type of file?
After figuring out how to get them into Excel we can deal with how to
analyze the data. For a start I would suggest a separate sheet for each
section. You will want some kind of indicator for the 'section of the
population' that took the tests - if that ends up being the division of data
by sheet, that's fine for now. But you also say you want to look at specific
characteristics of each student - where is that information coming from?


"Justin" wrote:

Hello everyone:

I have been asked to help work on a project involving achievement test
results. I would need to find a relatively painless way of inserting the
data and then comparing results over a period of eight years. Each section
of the achievement test has a raw score, a scaled score, a graded equivalent
and a percentile based on the section of the population taking the test.
There are five sections in total, one of which will only be taken by the
first level. How would I go about doing this? How could I then look at
specific characteristics of each student and see how that affects the data?
This is a little beyond my ability with excel but I said that I would give it
a shot.