Excel Challenge (prize - she keeps her job!)

i laid out a table in a very basic format.

group A1 was repeated along the rows 6 times for the Day class and 4
for the evening class. i filled up the rest of the values accordingly,
manually (no need for formulae).

turned Data Filter on, and i had all the answers in 5 mins. created
pivot table (not necessary for the lady to have known, hence not
important here).


---
Message posted from http://www.ExcelForum.com/

"Jerry W. Lewis" wrote in message ...
Column A lists Courses (A3:A9)
Rows 1 & 2 give column headings

B3:G9 lists # students in daytime groups (many are blank)
H3:K9 lists # students in evening groups (many are blank)
=SUM(B3:K9) is total number of students

L3:L9 lists # hours per daytime group (L3:L6 are identical)
M3:M9 lists # hours per evening group (M3:M6 are identical)
=(SUMPRODUCT(B3:G9,ISNUMBER(B3:G9)*L3:L9)+SUMPRODU CT(H3:K9,ISNUMBER(H3:K9)*M3:M9))*18
is total number of learning hours per 18 week program

You could also add students/learning hours per course, and dress up with
borders, etc. Total time (including formatting) 10 minutes

Jerry

Thanks Jerry this is very helpful. Could I ask about your level of
skill and experience and would it be possible for you to email us a
copy of your spreadsheet. Many thanks.

Thanks Myrna, is there any chance you could email us with your
solution.

Regarding her employment situation, she works for a college (which can
actually give her the required training) and is at this stage going
through the internal appeals procedure. The test was set by a person
unknown within the college and not her direct boss so we are hopeful
at this stage that she could win her job back without too much ill
feeling. If she loses her appeal, then she will consider a legal
remedy, which as you say, could make life more difficult. At age 50
and only ever having two jobs since leaving school, she is determined
to try to hang onto this job because she is not too hopeful about the
employment prospects out there for someone in her situation.

Many thanks to everyone that has responded so far. We have had about
10 quality replies that she can use as reference points. If anyone
else wants to have a go at the test, we would be very grateful.

I have worked with computers (primarily statistics packages and Fortran)
for nearly 3 decades. I was aware of Excel previously, but only started
using it 1996. I started participating in these newsgroups in September
of 1997, and was named an Excel MVP this past January.

Jerry

Her Husband wrote:

"Jerry W. Lewis" wrote in message ...

Column A lists Courses (A3:A9)
Rows 1 & 2 give column headings

B3:G9 lists # students in daytime groups (many are blank)
H3:K9 lists # students in evening groups (many are blank)
=SUM(B3:K9) is total number of students

L3:L9 lists # hours per daytime group (L3:L6 are identical)
M3:M9 lists # hours per evening group (M3:M6 are identical)
=(SUMPRODUCT(B3:G9,ISNUMBER(B3:G9)*L3:L9)+SUMPRO DUCT(H3:K9,ISNUMBER(H3:K9)*M3:M9))*18
is total number of learning hours per 18 week program

You could also add students/learning hours per course, and dress up with
borders, etc. Total time (including formatting) 10 minutes

Jerry


Thanks Jerry this is very helpful. Could I ask about your level of
skill and experience and would it be possible for you to email us a
copy of your spreadsheet. Many thanks.

Thank you to everybody that has tried the test and sent me their Excel
file. It is becomming obvious that the test is not as easy as it
would first appear or the question is somewhat ambiguous since we have
received back many different results. I have produced a table below
of the results from the 9 respondees so far. The 1st column is the
time taken in minutes, the 2nd is the number of years experience of
Excel and the 3rd and 4th columns are the answers to the test.

Time Yrs Exp Students Hours
---- ------- -------- -----
20 10 395 931,410
10 8 412 53,892
10 5 412 3,726
28 12 412 2,994
50 7 98 53,892
20 1 412 3,726
13 14 412 3,726
20 3 412 3,726
30 10 412 3,726

The fact that even the 'experts' disagree is very helpful to my wife's
case about her employers expectations of what a novice should be able
to produce under exam conditions.

Would some more people like to try - please email your file together
with a note about your level and years of experience and how long the
test took, together with any other observations. The test again is:

THE TEST
Present the following information in an accessible table format

The following training programmes are run in a college

Course A1, 10 groups (6 day and 4 evening)
Course A2, 5 groups (3 day and 2 evening)
Course A3, 3 groups (2 day and 1 evening)
Course B, 2 groups (1 day and 1 evening)
Course C1, 5 groups (3 day and 2 evening)
Course C2, 3 groups (2 day and 1 evening)
Course C3, 1 group (day)

15 enrol on each group in the A Courses and 17 in Course B. 12 enrol
in each group for the C courses.

All groups run for 18 weeks. Day-time groups are 10 hours a week (per
group) and the evening groups are for 5 hours a week (per group).
Courses C1 & C2 groups run for 4 hours a week each. Course C3 is 15
hours a week.

Calculate the total number of students enrolled and the total number
of learning hours for the 18-week programme.

I am curious how the question was interpreted by those who got 3726 hours?

Jerry

Her Husband wrote:

Thank you to everybody that has tried the test and sent me their Excel
file. It is becomming obvious that the test is not as easy as it
would first appear or the question is somewhat ambiguous since we have
received back many different results. I have produced a table below
of the results from the 9 respondees so far. The 1st column is the
time taken in minutes, the 2nd is the number of years experience of
Excel and the 3rd and 4th columns are the answers to the test.

Time Yrs Exp Students Hours
---- ------- -------- -----
20 10 395 931,410
10 8 412 53,892
10 5 412 3,726
28 12 412 2,994
50 7 98 53,892
20 1 412 3,726
13 14 412 3,726
20 3 412 3,726
30 10 412 3,726

The fact that even the 'experts' disagree is very helpful to my wife's
case about her employers expectations of what a novice should be able
to produce under exam conditions.

Would some more people like to try - please email your file together
with a note about your level and years of experience and how long the
test took, together with any other observations. The test again is:

THE TEST
Present the following information in an accessible table format

The following training programmes are run in a college

Course A1, 10 groups (6 day and 4 evening)
Course A2, 5 groups (3 day and 2 evening)
Course A3, 3 groups (2 day and 1 evening)
Course B, 2 groups (1 day and 1 evening)
Course C1, 5 groups (3 day and 2 evening)
Course C2, 3 groups (2 day and 1 evening)
Course C3, 1 group (day)

15 enrol on each group in the A Courses and 17 in Course B. 12 enrol
in each group for the C courses.

All groups run for 18 weeks. Day-time groups are 10 hours a week (per
group) and the evening groups are for 5 hours a week (per group).
Courses C1 & C2 groups run for 4 hours a week each. Course C3 is 15
hours a week.

Calculate the total number of students enrolled and the total number
of learning hours for the 18-week programme.

Hi Jerry

i found the question - the total number of learning hours for the 18-week
programme ambiguous - does it mean the total number of hours that the rooms
are occupied for training (for which i got 3,726) or the total number of
"man-hours" undertaken in learning (for which i got
184,032) - however previously in the question ...
"Day-time groups are 10 hours a week (per group) and the evening groups are
for 5 hours a week (per group). Courses C1 & C2 groups run for 4 hours a
week each. Course C3 is 15 hours a week."
possibly ambiguous information is again supplied which might account for the
other discrepencies in the answers.

Cheers
JulieD

"Jerry W. Lewis" wrote in message
...
I am curious how the question was interpreted by those who got 3726 hours?

Jerry

Her Husband wrote:

Thank you to everybody that has tried the test and sent me their Excel
file. It is becomming obvious that the test is not as easy as it
would first appear or the question is somewhat ambiguous since we have
received back many different results. I have produced a table below
of the results from the 9 respondees so far. The 1st column is the
time taken in minutes, the 2nd is the number of years experience of
Excel and the 3rd and 4th columns are the answers to the test.

Time Yrs Exp Students Hours
---- ------- -------- -----
20 10 395 931,410
10 8 412 53,892
10 5 412 3,726
28 12 412 2,994
50 7 98 53,892
20 1 412 3,726
13 14 412 3,726
20 3 412 3,726
30 10 412 3,726

The fact that even the 'experts' disagree is very helpful to my wife's
case about her employers expectations of what a novice should be able
to produce under exam conditions.

Would some more people like to try - please email your file together
with a note about your level and years of experience and how long the
test took, together with any other observations. The test again is:

THE TEST
Present the following information in an accessible table format

The following training programmes are run in a college

Course A1, 10 groups (6 day and 4 evening)
Course A2, 5 groups (3 day and 2 evening)
Course A3, 3 groups (2 day and 1 evening)
Course B, 2 groups (1 day and 1 evening)
Course C1, 5 groups (3 day and 2 evening)
Course C2, 3 groups (2 day and 1 evening)
Course C3, 1 group (day)

15 enrol on each group in the A Courses and 17 in Course B. 12 enrol
in each group for the C courses.

All groups run for 18 weeks. Day-time groups are 10 hours a week (per
group) and the evening groups are for 5 hours a week (per group).
Courses C1 & C2 groups run for 4 hours a week each. Course C3 is 15
hours a week.

Calculate the total number of students enrolled and the total number
of learning hours for the 18-week programme.

Hi!

20 minutes was enough for me to complete, add a bit more information
and do some tidying formatting.

I had weeks in C, Hours in D, Groups in E and Students per group in F.
Each course was given two rows; one for day and one for evening.

Key formulas we
Total students
=SUMPRODUCT(E2:E15,F2:F15)
Returned 412

Total Taught Hours:
=SUMPRODUCT(C2:C15,D215,E2:E15)
Returned 3726

Total Student Hours:
=SUMPRODUCT(C2:C15,D215,E2:E15,F2:F15)
Returned 53892

It wasn't totally reliant upon SUMPRODUCT as you could get some value
out of calculating horizontally and summing the vertical results. I
did this as a crosscheck and to provide additional information. Weeks
was common at 18 but I like to give flexibility.

As an academic, I'd say that the wording of the question could have
been clearer.

As a test it doesn't reveal very much apart from the ability to deduce
a logical layout of the data. It only required the use of 1 function
(or 2 if you did a crosscheck) and that makes it difficult to develop
any grading of the examinees.

Time allowed was just adequate and no more. My personal view on
computer examinations in Excel is that the time allowed should be
generous as that allows more thought to be given on layout and
flexibility. With more time than necessary, the better students would
be expected to incorporate clear formats, protection and perhaps data
validation.

A single question examination in my view is not appropriate as a test
of skill level.

My own experience? Academic teaching of Excel. 10 years use of Excel
plus 5 years prior of Lotus 1-2-3.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

"Her Husband" wrote in message
om...
"Jerry W. Lewis" wrote in message
...
Column A lists Courses (A3:A9)
Rows 1 & 2 give column headings

B3:G9 lists # students in daytime groups (many are blank)
H3:K9 lists # students in evening groups (many are blank)
=SUM(B3:K9) is total number of students

L3:L9 lists # hours per daytime group (L3:L6 are identical)
M3:M9 lists # hours per evening group (M3:M6 are identical)
=(SUMPRODUCT(B3:G9,ISNUMBER(B3:G9)*L3:L9)+SUMPRODU CT(H3:K9,ISNUMBER(H3:K9)*M3:M9))*18
is total number of learning hours per 18 week program

You could also add students/learning hours per course, and dress up
with
borders, etc. Total time (including formatting) 10 minutes

Jerry

Thanks Jerry this is very helpful. Could I ask about your level of
skill and experience and would it be possible for you to email us a
copy of your spreadsheet. Many thanks.

"Doug Kanter" wrote in message ...
How much was she off by, in terms of time? Maybe she's not cut out for that
kind of work.

Hi Doug, she was given 5 minutes to read during which she wasn't
allowed to make any notes and twenty minutes access to the computer.
She didn't complete the task in the time. She was beginning to think
the same as you, but as you can see, even the 'experts' haven't all
agreed and this has actually fired up her confidence and boosted her
self esteem. Thank you to everybody that has helped - this exercise
has proved to be most beneficial.

Who were the Excel experts?
Just a thought, if she is an admin, then a test like that is really
ridiculous unless they used
it just to get rid of her, in which case it would be crooked to say the
least.


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Her Husband" wrote in message
om...
"Doug Kanter" wrote in message
...
How much was she off by, in terms of time? Maybe she's not cut out for
that
kind of work.

Hi Doug, she was given 5 minutes to read during which she wasn't
allowed to make any notes and twenty minutes access to the computer.
She didn't complete the task in the time. She was beginning to think
the same as you, but as you can see, even the 'experts' haven't all
agreed and this has actually fired up her confidence and boosted her
self esteem. Thank you to everybody that has helped - this exercise
has proved to be most beneficial.