# Edexcel Applied ICT Unit 3 exam Jan 2012

Discussion in 'Computing and ICT' started by colwynexile, Dec 1, 2011.

1. ### mumfy612

Is the general consensus that the information for the Season Weeks sheet will not come from a dataset? Pupils chose from two datasets and this forms (together with the AHHL import) the questions for Activity 2? Struggling to think that the AHHL Import will form the whole of Activity 2.

2. ### vornstarNew commenter

It says on the scenario that the values are CALCULATED.

3. ### mumfy612

Thank you for the capitals!
How about an IF statement that highlights if the property is fully booked or not.....?
Straws clutched.

4. ### mumfy612

Ignore that last post

5. ### MBulmer1983New commenter

I believe season weeks A8 will be roughly
=ROUND((INDEX('SP Calc'!\$A\$7:\$U\$980,MATCH('Rental Setting'!B8,'SP Calc'!\$A\$7:\$A\$98),MATCH(Ratings!N9,'SP Calc'!\$A\$7:\$U\$7))*\$B\$19),0)
Give or take some brackets.
Once you understand index and match you'll whould see how I got to this and be able to work the rest out.

6. ### vornstarNew commenter

Sorry about the capitals - was in a rush and intended to write more - in the middle of a progress day with parents :-(

7. ### vornstarNew commenter

Surely they can't be expecting anything as complicated as that? I hope not anyway - half of mine are struggling to remember how to use an IF statement correctly.

8. ### MBulmer1983New commenter

<font size="2">If anyone can see and easier solution that works then please share it. The formula I have given works and isn&rsquo;t too difficult to understand when broken down into its component parts. It&rsquo;s putting it all together that&rsquo;s the difficult bit.</font>
I cannot see where a hlookup or vlookup can be useful in this scenario.

9. ### vornstarNew commenter

I'll give your idea a go to see what it does. Don't fancy teaching it mind.

10. ### seejaytay

As far as I can tell, once the rental amount is set, this will give you the predicted occupancy (based on the property rating and the season). You can then multiply the occupancy percentage by the maximum possible weeks to get the overall occupancy of that house.

Does that make sense to anyone else?
Cheers

11. ### DEmsleyNew commenter

Easier than INDEX/MATCH? It *IS* A level!
I think you've posted too much and that the board will get this thread pulled shortly as we're now going to see many, many students with a basic copy of the formula above that they've learnt by rote rather than understanding it.
BTW there are many named areas of the spreadsheets to explore and that will help a great deal with the "season weeks"

12. ### MBulmer1983New commenter

If the board didn?t want discussion of the models formulae then they wouldn?t give us the model over a month before the exam window.

Regarding the difficultly. I agree that use of index and match functions should be expected of A? level student. However, it?s crazy to ask them to use them and a round and a multiplication all in one cell. While this might be advantageous in making an elegant spreadsheet in the real world from an assessment point of view it?s an aberration. So many skills in one formula will make it an all or nothing question for many students.

13. ### DEmsleyNew commenter

I totally agree with you on this. It's way beyond anything that they have ever asked for before - or are we reading too much into it? Do they expect this depth, or are we over-complicating it?
However I cannot see a simpler solution!

14. ### seejaytay

It can be done with a VLOOKUP and MATCH function - little bit clearer and easier to teach than index and 2 matches...... possibly. That way, the VLOOKUP finds the correct row and then the MATCH function finds the relevant column number.

How does that sound?

15. ### vornstarNew commenter

I understand the theory behind using vlookup and match, but I can't get it to work. However, I got INDEX/MATCH version to work. It's made easier by the fact that the ranges have been given names (SPCashRange, SPRatings and SPMatrix) which should make it easier for students to understand.

16. ### Steve Carter

I'm ready to be shot down in flames here, but I don't think the solution to the Season Weeks sheet lies in the use of relatively complex functions; I think it's much simpler than that.
The scenario states that the Season Weeks sheet:
"...calculates the number of weeks that each property is available during each season."
The key here is the use of the word 'available', as opposed to 'used' or 'occupied'. Only properties 4-7 are available during the ski season, the others are not. Consequently, I think we just need to use an IF statement that looks at the values in Rental Settings cells B8:E17. If there's a rental price there, it returns the relevant available weeks value (cells B19:E19 on Seasons Weeks) and if there is a zero (as for properties 1-3 and 8-10), it returns a zero.
I feel this is more in-keeping with the nature of the exam and is in-line with what has happened in previous models. It also requires students to use an IF function, which has been a 'given' in the past.

17. ### MBulmer1983New commenter

Steve I think you're right. The index and match funtions are used in the revenue worksheet.

18. ### Steve Carter

I hope so! Whilst a complex function may well work, it's beyond the scope of this exam - IMHO. Although it is an AS exam, Edexcel do state themselves about Unit 3:
"Just bear in mind, though, that this unit is about using spreadsheet
models, not creating them - that comes later if students opt for Unit
11."

19. ### chrisbarnes

Something I intend to contact Edexcel regarding - if you look at the revenue formula ='Rental Setting'!B8*INDEX(SPMatrix,MATCH('Rental Setting'!B8,SPCashRange),MATCH(Ratings!N9,SPRatingRange))*'Season Weeks'!B8 - this multiplies the rent you have set by figure found by the index and match and finally multiplies that figure by the relevant season weeks figure.
So it is NOT the number of weeks available that needs to be calculated but the number of weeks occupied of those available that is being calculated
I wish your scenario were correct, but all formulae relying on the figure found in the season weeks sheet would be incorrect if it was the case.

20. ### Steve Carter

That may well be the case, but perhaps we can assume that it's one of the shortcomings of the model, and something that students can get credit for pointing out in Activity 5?