Excel Challenge (prize - she keeps her job!)

[Her Husband]

This is an appeal to all Excel users out there. My wife has just lost
her job because she didn't satisfactorily complete an Excel task in
the allotted time. We've put the actual Excel question up on our
website at www.dmhcipr.icom43.net. Please have a look. She is
appealing against her dismissal and would be grateful for any
constructive comments anyone might have - they just might help her win
her job back. Thanks for looking.

 

[Debra Dalgleish]

Post the Excel question here, and someone may be able to help.

 

[Norman Harker]

Hi "He who must do what he is told"!

Very few people will visit your (unknown) website for fear of what
might be picked up. Post your example in the body of a plain text
e-mail if possible and you'll surely get detailed responses.

Whether or not the question is fair will depend upon the question
itself (often ambiguous) and the amount of time involved.

In general terms, generous time allowances are better in ascertaining
the precise skill levels of examinees as they allow incorporation of
data validation, protection, conditional formats, table formats,
charts, pivot tables UDFs and Subroutines. The more skilled examinee
will have more time to provide more than a quick and dirty solution.

 

[Her Husband]

Post the Excel question here, and someone may be able to help.

Thanks Debra for the suggestion. If we can get about 100 people to do
this and send to our email address their Excel file to include also a
note of their years and level of experience and how long it took them
to do, then we might be able to present a statistical argument as to
the fairness of the test.

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.

 

[Jerry W. Lewis]

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)+SUMPRODUCT(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

 

[Myrna Larson]

This is just an "aside". If your description of the situation is correct --
she had minimal training in Excel, it was 8 years ago, and she doesn't use it
in her day-to-day work, then expecting her to accomplish this task in 20
minutes is not reasonable. Use of Excel is presumably not part of her job
description. Why is she being tested on something she isn't required to do?

OTOH, if she uses Excel routinely, it would be OK. The only "trick" that I see
is the need to create two rows for each group, one for the day class and
another for the evening class, since they have a different number of hours per
week. A person who works with Excel several times a week should have no
problem with it.

Anyway, I've been using spreadsheets for at least 25 years. It took me 12
minutes to set up a table and get the required totals.

But your wife's current situation. Let's say she sues the company, she wins,
and she gets her job back. What kind of atmosphere will she have to endure? I
sure wouldn't want to work under those conditions!

Yes, of course there are rules re retaliation on the part of the employer, but
such behavior can be very subtle and difficult to prove in court. Even if she
wins the suit, I expect she will eventually decide the situation is too
unpleasant and look for another job. And the job hunt may be difficult because
she has identified herself as a "trouble maker" or "difficult employee" (even
though, in this case, I think the test was unfair and the employer is at
fault). If I were your wife, I would start the job hunt now. And be sure the
job requirements are spelled out in full!

 

[Myrna Larson]

Hi, Jerry:

Your expertise is showing! I sure hope that employer is not expecting
"occasional" Excel users to handle SUMPRODUCT formulas as a replacement for
array formulas <vbg>.

Myrna Larson

 

[Her Husband]

Hi "He who must do what he is told"!

Very few people will visit your (unknown) website for fear of what
might be picked up. Post your example in the body of a plain text
e-mail if possible and you'll surely get detailed responses.

Thanks Norman, the test question is below. If I can get about 100
people to do
this and send to our email address their Excel file to include also a
note of their years and level of experience and how long it took them
to do, then we might be able to present some evidence in support of
her appeal against redundancy. Thanks to everyone who has submitted
examples so far.


The question was:

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.

-----------------------------------------------------

 

[Doug Kanter]

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

 

[icestationzbra]

i laid out a table in a very basic format.

group A1 was repeated along the rows 6 times for the Day class and
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. create
pivot table (not necessary for the lady to have known, hence no
important here)

 

[Her Husband]

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)+SUMPRODUCT(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.

 

[Her Husband]

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.

 

[Jerry W. Lewis]

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]

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.

 

[Jerry W. Lewis]

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

Jerry

 

[JulieD]

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

 

[Norman Harker]

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 were:
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.

 

[Her Husband]

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.

 

[Peo Sjoblom]

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]

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.

Thanks for the comments, Peo. By 'experts' I mean the people in this
newsgroup and another Excel bulletin board that kindly took the time
to have a go at the question. Without mentioning any names, many have
many years experience at professional level. This is in comparison to
her, who has only minimal experience. I have produced an updated grid
at the bottom of this posting showing the results from 24 replies.

In relation to your other comments, we think you have hit the nail on
the head. Some people have asked why would she want to carry on
working there - well she has been there for 15 years, loves her job
(she works in a college and supplies an admin service to disabled and
disadvantaged students), is prepared to be trained (in fact she
specifically asked for spreadsheet training) and is not going to let a
new upstart manager use these tricks against her.

Results of 24 replies to original posting showing time taken to
produce spreadsheet, the number of years experience and the answers to
the 2 questions:

Time Yrs No. No.
(mins) exp. stdnts hours

10 8 412 53892
10 10 412 3726
10 447 1091
13 14 412 3726
13 452 3176
17 3 412 53892
20 10 395 931410
20 1 412 3726
20 3 412 3726
20 8 412 53892
20 15 412 3726
28 12 412 2994
30 15 98 60372
50 7 98 53892
50 7 412 53892
13 20 412 53892
30 10 412 3726
9 412 93474
30 412 23436
17 3 412 53892
40 6 412 2406
15 8 412 3726
15 7 412 3726
412 3726

Analysis of answers to number of students

Answer Frq Percent
412 19 79.2%
98 2 8.3%
447 1 4.2%
452 1 4.2%
395 1 4.2%

Analysis of answers to number of learning hours

Answer Frq Percent
3726 9 37.5%
53892 7 29.2%
1091 1 4.2%
3176 1 4.2%
2994 1 4.2%
60372 1 4.2%
93474 1 4.2%
23436 1 4.2%
2406 1 4.2%

Average time taken to produce spreadsheet - 21.7 minutes (range 9 - 50
minutes)
Average years experience of respondants - 8.8 years (range <1 to 20
years)

Many thanks to everybody that replied - all this information is very
helpful.