SPSW - Coursework from BPP - Kitchens

Adele69

For those who have done the Spreadsheet Software module with BPP books and course material. I'm struggling through the question on Kitchens, as part of the additional coursework. We got a handout with the questions, the files and a printout of answers. The printout of the answer has no formulas, so no real help in getting the answer.

The Kitchens workboot comprises four worksheets: Order Details, Types, Prices and Data. The last can be ignored as this was copied into prices as part of the earlier tasks. In the Order Details sheet there are two tables. The first table has a cell C5 for Style (these are listed in types: 2 = White, 3 = Pine, 4 = Beech, 5 = Oak, 6 = Teak). The second table has several columns for Unit, Description, Quantity, Price, Total Price. Unit and Description are defined, the others are calculated from lookups and sums.

The Price is based on the value in Prices, and the particular column is based on Type, so a lookup within a lookup, but I'm getting a value error presumably because one lookup is showing text not number. This was my formula attempt which gave me an error.

=VLOOKUP(B8,Prices!A3:H36,(VLOOKUP('Order Details'!$C$5,Types!$A$3:$B$8,2,FALSE)),FALSE)

I have an exam on Tuesday. Last week was college half term (same holidays as schools - really inconvenient). I was fairly confident after doing the AAT practice assessment before looking at this, and without the answers my confidence has evaporated.

UPDATE: Just having something to eat after sweating over this 2-3 hours, and found the result was much simpler (actually says +2 in the instructions but when I added the column results it should be +1 for the correct Type price). Still miffed I took so long in arriving at the answer.

=VLOOKUP($B8,Prices!$A$3:$H$36,($C$5 +1),FALSE)

KoopaCooper

Hi there,

I just did this exact question this morning - the tutor had to go over it with the class, apparently a lot of us (11 total) were struggling with it, although I got it in the end (once I noticed that the notes at the bottom of the question basically told me exactly what to do - lol! xD).

Our tutor did say one thing that was encouraging - apparently, this was a deliberately tricky question, and we wouldn't be likely to get anything near as convoluted as this in the final assessment, but if you can do this, the real thing should be a breeze by comparison. Which is good.

Anyway, got my mock assessment for SDST next Tuesday, and the real assessment is actually being offered for next Tuesday evening, so I might do it on the same day just to get a 1-week holiday from college.

Best of luck with your own SDST exam!