/
Review of my spreadsheet Review of my spreadsheet

Review of my spreadsheet - PowerPoint Presentation

pamella-moone
pamella-moone . @pamella-moone
Follow
427 views
Uploaded On 2017-01-26

Review of my spreadsheet - PPT Presentation

By Nathan Scotchford All the different pages Enhanced spreadsheet Main Menu Pivot table Trip data House stats Review of all the different cells and pages First name and Surname These cells are text cells and dont need have any sort of protection cell validation or conditional formatt ID: 514189

trip data points formula data trip formula points student cells house cell total trips change spreadsheet achievement form wrong

Share:

Link:

Embed:

Download Presentation from below link

Download Presentation The PPT/PDF document "Review of my spreadsheet" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.


Presentation Transcript

Slide1

Review of my spreadsheet

By Nathan ScotchfordSlide2

All the different pagesSlide3

Enhanced spreadsheetSlide4

Main MenuSlide5

Pivot tableSlide6

Trip dataSlide7

House statsSlide8

Review of all the different cells and pagesSlide9

First name and Surname

These cells are text cells and don’t need have any sort of protection, cell validation or conditional formatting as they may need to be changed. For example the child's second name may change due to his family name changing, in the event of this the school could easily edit the and change the data without any errors popping up.Slide10

Year group

The year group cells are numerical and have cell validation, this is to stop people accidently inputting the wrong year for that student, it is also there as a way to stop vandalism. As you can see at the bottom when you input the wrong data you or prompted with an error message letting you know that you can’t enter that data unless the value is between 9 and 11.Slide11

The form tutor cells have cell validation, this again stops the user inputting the wrong data. It also has a drop down menu with the list of all the form tutors, this is for ease of access and to stop people from vandalising this important data cells. If you do input the wrong data you will be prompted with the error message shown below.

Form tutorSlide12

The house cell displays what house that student is in, it uses a formula to do this =VLOOKUP('Enhanced spreadsheet'!D3,'Trip data'!$A$1:$B$8,2,FALSE) this formula looks up the form, tutor of that student and then uses it to determine which house that student is in. It uses a second page for this with all the data on to find out what house that student is in.

HouseSlide13

Total points

These cells again are numerical, but don’t have any formula or data editing device on them. They are simply there in order to help another formula later on in the spreadsheet.Slide14

Net Points

The Net points cell takes the Total Behaviour points away from the total Achievement points. Using the formula =F2-G2 specific to that cell. It isn't used for anything else its purely there for the used of the teachers or whoever is going to use the spreadsheet.Slide15

Outside trips

The outside trips cells use formula which takes into account the students total Achievement points and total Behaviour points, it then uses this data to determine which student is going on which trip, whether this be inside or outside.

=IF(F2>100,"X"," ")- Alton towers

=IF(F2>=91, IF(F2<=100,"X"," ")," ")- Sea Life

=IF(F2>=81, IF(F2<=90,"X"," ")," ")- Paintballing

=IF(F2>=71, IF(F2<=80,"X"," ")," ")- CinemaSlide16

Inside trips

The inside trips also take into account the students total Achievement points and total Behaviour points except for a different cell set.

=IF(F2>=61, IF(F2<=70,"X"," ")," ")- Art day

=IF(F2>=51, IF(F2<=60,"X"," ")," ")- Cheerleading

=IF(F2>=41, IF(F2<=50,"X"," ")," ")- Football Skills

=IF(F2>=31, IF(F2<=40,"X"," ")," ")- T-Shirt Design

=IF(F2>=21, IF(F2<=30,"X"," ")," ")- X-BoxSlide17

The cost takes into account all the costs of all the trips from the data page and selects the right price depending on which trip that student is going on. The formula used is very complicated, however it does make it very easy for the user to change that data with out having to change the formula itself. And because the formula all works in sync with the data changing the trip of a student will change the price of shown on that students price cell.

=IF(I2="X",30,IF(J2="X",25,IF(K2="X",10,IF(L2="X",7,IF(M2="X",5,IF(N2="X",7,IF(O2="X",3.5,IF(P2="X",10,IF(Q3="X",6,0)))))))))

CostSlide18

These cells use a massive piece of formula, I takes into account all the choices of trips there are Inside and Outside and uses which trip the student has been chosen for to determine what is inputted, if the students amount of achievement points where to change to a point where the surpass the need points to go on a better trip, then the data would modify itself changing that and the final choice

.

=IF(I2="X","

Altontowers

",IF(J2="X","

SeaLife

",IF(K2="

X","Paintballing",IF

(L2="

X","Cinema",IF

(M2="X","

Artday

",IF(N2="

X","Cheerleading",IF

(O2="

X","Football

Skills",IF

(P2="X","T-shirt

Design",IF(Q2="X","X-Box",""))))))))).

Final ChoiceSlide19

Date of trip

The date of trip cells use a formula that finds out what trip that student is attending then uses the data from another page which has a what trips are on what days, this then translates into dates which can be seen on the right. The formula used is as follows, =VLOOKUP(S2,'Trip data'!$A$12:$B$24,2,FALSE).Slide20

Dead line date

The deadline date is very straight forward, it uses a formula to select the deadline of the trip that that student is going on, again if the trip changes then the deadline will change to the deadline of that trip.

=T2-7Slide21

Pivot table

The pivot table is there to show the total accumulated achievement points a teachers class has earned it is also there so that in case the user can read spread sheets they can look at this and understand.Slide22

Trip data

The trip data is there to allow the creator to make formula to increase the ease of access for the user I has a list of all the teachers, what house they belong and their form name. I also has the external trips, their date cost and maximum number of pupils allowed on that trip as well as all that data for the internal trips.Slide23

The main menu

The main menu is where all the buttons are to easily access all the different pages. These consist of the Pivot Table, the Enhanced Spreadsheet, the Trip data and House stats. Clicking on a button will take you that page.Slide24

House stats

This sheet has all the data of the houses, it includes the total amount of Achievement points and Behaviour points of each house shown in a box chart and bar chart.Slide25

Overall review

Overall I feel that the spread went very well all the formula works in sync with each other and there are no errors. The conditional formatting has highlighted the important points and grouped the different sections sufficiently. The cell protection and validation made it easier for the user to change data and not input the wrong data, as well as stopping any kind of vandalism. The one thing I would point out is that in the event that the spreadsheet breaks, a normal teacher with no experience in excel would not know how to fix it, especially the long and complicated formula.