# ICT Unit 3, June 2010 exam

Discussion in 'Computing and ICT' started by maloa001, Apr 11, 2010.

1. ### maloa001

I have started this post in order to discuss the pre-released,As level, Unit 3 scenario. For those who still haven't recieved these, please leave your email address and i will make sure to provide you with the documents.

What are your thoughts on the scenario?

2. ### BrianUKNew commenter

Do you mean AQA INFO3?

3. ### maloa001

No, i mean the Edexcel Unit 3 - Knowledge Worker

It seems straight forward enough. They are obviously holding back any data on the best 15 players until the exam. So far I see a good solution as being one that inludes all 30 best players and gets to Crawley by 12:00 but also sets off at a reasonable time (minimising driving time). All very obvious so I have probably missed something.
I haven't spotted any errors in the formulae yet. Has anybody else?

5. ### monicat

I've just had a very quick look but I'm assuming that the given data files (Times practice) gets imported in the Timings wrksheet, the model then calculates the travelling time.
We'll need a simple add formula to add together boys and girls for each clubs in the Teams worksheet. I'm also assuming that we need a formula that adds waiting time (5 mins? maybe more? maybe given in the exam???) to the arrival time to come up with a departure time in the worksheet Timetable for column E, H, K, N Q.
I think column B requires manual modelling to make sure arrival time at crawley is before middays (it's conditional formatted to go red if too late). Anybody tried to add time? It's not very straitforward.
I agree with chad78 that a good solution seems to be one that includes all 30 best players and gets to Crawley by 12:00 but also sets off at a reasonable time (minimising driving time), however I can only see this working for 1 of the training days, what abut the other 4 age groups? The scenario stated that each age group (B+G) will train on a different day, do they expect 5 different solutions (one for adults, one for under 12s etc) or I am reading this wrong?

There are a couple of ways to do this, the easiest I think would be to use TIME function. i,e D4+TIME(0,5,0)). However, I don't think that goes far enough as the model doesn't function as intended if less than 5 stops per bus are used. Maybe what is needed is an IF statement with the TIME function embedded. The IF statement works out whether the previous destination has changed, if not it returns the same departure time as before.
Any other thoughts on this?

7. ### HappyHippy

I think do it the inefficient way (of the 3 different methods in my post from April 6th my favourite is D4+"00:05") and discuss in the evaluation. While selection (IF) is on the syllabus, I don't think they've ever been asked to use it in this complex a way in any previous papers. At worst I reckon students would lose 1 mark, but would easily pull that back in Section 5.

All supposition, of course...

8. ### donakebab

completed a solution to this but I think the coach driver is going to hate my start times! Of course whether to solution works in the exam will depend entirely how many children are actually being picked up at each point. I have just produced some of my own data to give the pupils as a mock exam

9. ### lulu26

Is it possible to get a copy of your mock exam, it would be really useful to have is possible.
Thanks!

10. ### cc4rhu

The IF statement on the timetable worksheet isn't that complicated and I think I'm going to try to teach that method to my students. Those who don't grasp the logic will have to settle for the +5 mins formula with the error. I agree that it'll probably only be a mark or two so it's not really worth worrying about.
Cc

11. ### monicat

It really depends on your test data! Goal seeking the departure time from Crawley helps optimising the solution to exactly 12:00 without wasting time modelling... It's my first year running this so is it quite common for this unit to have goal seek or am I thinking too much?

12. ### JayEmAyNew commenter

Aaargh! This formula from the timetable sheet is givving me a headache, could someone explain what it does please?
=IF(C4="No Stop",B4,B4+INDEX('Calculated Time'!\$A\$3:\$P\$18,MATCH(A4,'Calculated Time'!\$A\$3:\$A\$18,0),MATCH(C4,'Calculated Time'!\$A\$3:\$P\$3,0)))

Totally confused of Bristol.

13. ### JayEmAyNew commenter

Don't worry I've got it. The way to make sure they all leave 5 mins later is to add this to the departure time =IF(C4="No Stop","No Stop",D4+TIME(0,5,0)). I think.

14. ### chrisbarnes

IMHO a simpler formula for that would be =IF(F4=C4,G4,G4+TIMEVALUE("0:05")) or of course combining the two =IF(C4="No Stop","No Stop",G4+TIMEVALUE("0:05"))
I find students understand TIMEVALUE better, they don't make errors putting the minutes in as seconds.

15. ### chrisbarnes

The conditional formatting on the 'Teams' worksheet appears to turn cells C18 and D18 red if their value is greater than 15, surely this is incorrect?
I can understand this being the case for the total on each minibus but not the totals for the each Gender of Player to be picked up.
Also I was wondering where Edexcel will throw in the alternative data source? There will in a likelihood be an alternative 'times' data source, maybe the minibus company and a motoring organisation. There will obviously also be a list of the top players provided in the exam.

16. ### chrisbarnes

Actually have just tried this and it doesn't work as the spreadsheet automatically shows the last pickup point as the pickup point in the next available slot and therefore "No Stop" is not present so my original formula of =IF(F4=C4,G4,G4+TIMEVALUE("0:05")) is the one to use of course you might choose to use =IF(F4=C4,G4,G4+TIME(0,5,0)) with your students.

17. ### chrisbarnes

I think you have too many variables for a Goal Seek. You will be calling at more than one destination with each bus. Trial and error tens to be the way these things work in practice under exam conditions.

18. ### ajohnson55

Formulas using match and index used to confuse me but it is fairly straightforward.
This If statement checks to see if a new destination has been added for that stop. If it is No stop then it just copies the departure time from the previous destination. If it isn't, ie a new destination has been added, it uses match and index to do a sort of 2 dimensional look up on the Calculated time sheet to find the correct time to add to the previous departure time to calculate the next departure time. Eg If C4 is Southend and the previous place was Crawley at 6:45, then it adds 1:28 to 6:45 to get 8:13. The 1:28 is 'looked up' on the calculated time sheet by matching the postion of Crawley and Southend on this sheet and using those positions as indexes in the table to find the time between the 2 ie 1:28
Hope this helps. Students don't need to know the exact details of how this works, only what it achieves.

19. ### colwynexileOccasional commenter

Goal seek actually works (OMG) and comes up with a suitable / expected answer. The fact you have more than one destination does not affect the goal seek as it gets into circular references and works backwards to the start point. However...

1) the paper says they need to register by 12 noon, so 12 noon arrival will be too late
2) the conditional cells in c and d 18 are to make sure you only take 15 of each gender altogether at the event - however this will not be a problem as its in a yellow box and therefore will be the extra file we need to import on the day.
3) have you noticed the hidden cell on the first sheet - why? I can't find a purpose for it?
4) as for the formula, instead of using TIMEVALUE or (0,5,0) just have the arrival time cell and then go..+"00:05:00" The speech marks makes sure the time format remains in place - others aren't wrong, just easier to explain to my kids.

20. ### uselessnerd

any chance of getting a worked version of the model please, I will trade for a mock paper I am currently working on