Help needed on Excel

need to create a big spreadsheet, dont know how.

Published on December 11, 2005 By Ziggystyles In Life Journals

Hi all,
Im not writing nearly as much as I thought...usually when Im at work and have some free time (perks)...I get online and post something (hence my job postings)....but Ive been slacking off. Nothing of importance to write about.

But...in my new position as supervisor of.........a room that students are sent to when they misbehave, trying to stay somewhat anonymous on here in a sense; I figured it would be good to present the schools administration with a spreadsheet.

What Im trying to do is to create a spreadsheet...with the end goal of totalling things up and making graphs that holds a bunch of information...but Im not sure how to make it. I am using some tutorials on excel and that is helping me, but Im still not sure what Im doing.

The goal of this is to make something that I can present to the administration showing them some statistics on the students who are sent to my room for misbehavior....so that they can see that 80% of the people who come in are guys....or 20% are of x ethnicity...etc so that they can use the graph and maybe make changes for the next school year to get less kids coming in.

Id like to be able to enter in the students last and first name, their grade, sex, ethnic code, teacher who sent them, code for why they were sent down and if it was for the hour or for the day. I think I have other information as well.

What Im doing is pretty much entering the information down. I have columns for the above mentioned items....but I dont know if Im even doing it right or even how to do what I want to do. I dont know how to compile the information, such as figureing out how many of each sex was sent down, or which teachers sent down the most/least....etc; maybe I could get stats for the students that the teachers send....meaning...maybe a teacher sends down only hispanic kids.

How do I do this?

Suggestions?
Thanks!

Comments

Dan Kaschel

on Dec 11, 2005

It may be possible to tally non-numeric data, but I think it'd be hard. I think probably if you don't want to get into some pretty complicated functions or VB office automation, you'll have to assign values to each possibility. For instance, Males could be 1 and Females 0. That way, the total for the column (To add the 19 values in column B, with B1 being the header, B21 would have the function "=sum(B2..B20)") will be the number of students who were male. Then, you could make a quick chart based on that number.

I'm not really that good with excel, but there's an idea of one way to do it.

Dan

lifehappens

on Dec 11, 2005

If you have access to Access....(pun intended) youmay find it a bit easier to manipulate the data. good luck!

Dr Guy

on Dec 11, 2005

Check the Help button.

bigrickstallion

on Dec 11, 2005

Well first of all its a database problem not a spreadsheet problem.
If you have access to Microsoft access then i can send you a basic working skeleton which you could immediately begin to work with.

There is no UI but given your requirements are very simplisitc, there isn't really a need for one at this stage. The advantage of a database is that it allows you to organise your data in way that promotes data reuse, reduces redundancy and answer a practically infinite number of questions (i.e reports) about your dataset from a single well known schema arrangement. There is also the advantage of then having everybodies favourite query tool, SQL, at your fingertips.

Using Excel on the other hand will lead to a dataset that is most likely very good at answering a particular question (thought of at the time of its development), but totally useless in terms of extensibility or answering questions that were not thought of at the time of development.

Using Excel as the datastore you will ultimately suffer from data redundancy and duplication and more importantly find yourself "stuck" with only being able to answer very basic questions due to the inflexible nature of the dataset schema.

Excel is fantastic tool for what if analysis and optimization problems but despite often being used as one, is not a very good choice for storing data.

So have you got MS Access?

Ziggystyles

on Dec 11, 2005

Dan,
Thanks for the advice...but I think that the thing is that there is so much that I want to do....I dont even know where to begin. I could put in numerical values for some fields....but Id have a hard time with getting the end results on many categories that cant really be put in as a numerical value. For example....discipline codes...I have a three letter code based on the reason why the student was sent in and Id like to be able to make a few graphs and results based on that information....but its hard to do.

Using Excel on the other hand will lead to a dataset that is most likely very good at answering a particular question (thought of at the time of its development), but totally useless in terms of extensibility or answering questions that were not thought of at the time of development.

brs,
The computer I use is at school so I will have to check when I go in to work. I think I do have it...and if not....I might be able to get it from the technology guys in the building.

bigrickstallion

on Dec 12, 2005

I might be able to get it from the technology guys in the building.

O.key doke well let us know.

Spectat0r

on Dec 12, 2005

You should use the COUNTIF Function.
COUNTIF sums up all the cells in a range that anser a certain criteria.
For example the line =COUNTIF(A2:A100,"Male") will count all the cells containing Male in them, that are in the A2:A100 range.

marchrhyme

on Dec 22, 2005

Spreadsheet seems too complicated and basically, you just need to present some data in a very simple and clear way:

here's what i'd do:

1. list down important statistics which you have on hand: e.g. gender, age/school group (e.g. grade 1, 2, etc), race (this is data that could easily mislead people or have them reach certain conclusions as your report could end up being some sort of racial profiling. maybe it's okay to have it on hand, but I wouldn't make this a main part of the report since there are other social factors involved in this and oversimplifying the matter won't help the administrators make good decisions), reason for detention (unacceptable behaviour in class/towards superiors/teachers, peer fights, tardiness, unreasonable absences from class, etc etc.

2. utilize pie charts and line graphs.

for line graphs, you can create 2 (one for female and one for males)

e.g. MALE LINE GRAPH : AGE AND DETENTION REASON ANALYSIS

- age is on the vertical side, detention reason on the bottom/horizontal part

(same for female)

Pie chart: would be the summary: e.g. (male, regardless of age, 80% go to detention room for petty fights, etc)

3. Data Table

Basically a tally list

Top portion: Gender - Age Group - Specific reason for detention - etc - etc

Underneath each heading: Stats or numbers for each heading

Hope this has been helpful!

Welcome Guest! Please take the time to register with us.
There are many great features available to you once you register, including:

Richer content, access to many features that are disabled for guests like commenting on the forums.
Access to a great community, with a massive database of many, many areas of interest.
Access to contests & subscription offers like exclusive emails.
It's simple, and FREE!