showing lookup result with multiple variables

Howdy neighbors,

I have been working on solving this one problem for three weeks, with no
success. I have been through the discussion forums, but not quite been able
to find the information I am seeking. Can you help? I would be very
appreciative of any suggestions you could provide.

The problem will be easiest to understand if you view the file, which can be
downloaded from my Comcast storage:

http://home.comcast.net/~jaredprice1...file_Sheet.xls

This is a spreadsheet used for scoring tests. This sweet spreadsheet will
automate everything once it is completed.

The problem at hand pertains to the "Math" sheet, cells D2-E10. Once I can
get those cells to function correctly, I simply need to get that information
into it's proper place on the "Score Summary" sheet and the long project is
complete.

There are four levels of tests: A, D, M, and E. Example: if the test being
scored is a level A, I will enter "A" into cell E16 on the "Score Summary"
sheet. This will affect several other cells, particularly those found in
columns B-D , which will be hidden. The Scale Score and Grade Level are
affected by the letter in this box and calculated accordingly.

The Scale Score and Grade Level is automatically calculated for Reading,
Language, Vocabulary, and Spelling. With Mathematics, however, there are two
tests, and the sum of correct answers are added into one raw score (cell
I15). However, the two math tests carry different amounts of weight, and so
the scale score and grade level has to be calculated from the charts provided
with the sheet (See "Math A", "Math D", "Math M", and "Math E".

Example: if test A was taken, and the tester scored 8 correct in Mathematics
Computation and 17 correct for Applied Mathematics, I would need to enter 8
into cell "Math!C2" and 17 into cell "Math!C7". I would then need the
information from "MathA!L39" to appear in cell "Math!D12" and the information
from "MathA!L40" to appear in cell "Math!E12". This information can then be
easily moved into the correct place the Score Summary sheet.

Another example, if necessary: if test D was taken, and the tester scored 20
on Mathematics Computation and 20 on Applied Mathematics, I would need to
enter 20 into cell C3 and 20 into cell C8 (on the "Math" sheet. I would then
need the information from "MathD!X45" to appear in cell "Math!D13" and the
information from cell "MathD!X46" to appear in cell "Math!E13".

Thank you so much for your assistance and ideas.

Jared Price
[email protected]

P.S. If you find yourself wondering about some of the less intelligent
design using lookup statements and such, just know that it seemed easier at
the time. I didn't build the four charts until later in the process.

It is not clear, at least to me why the A test would return "MathA!L39" to
cell "Math!D12" and "MathA!L40" to cell "Math!E12". I assume it has
something to do with those looked up values in D2,E2 etc., but I cannot see
what.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jprice" <[email protected]> wrote in message
news:[email protected]...
> Howdy neighbors,
>
> I have been working on solving this one problem for three weeks, with no
> success. I have been through the discussion forums, but not quite been
able
> to find the information I am seeking. Can you help? I would be very
> appreciative of any suggestions you could provide.
>
> The problem will be easiest to understand if you view the file, which can
be
> downloaded from my Comcast storage:
>
>
http://home.comcast.net/~jaredprice1...file_Sheet.xls
>
> This is a spreadsheet used for scoring tests. This sweet spreadsheet will
> automate everything once it is completed.
>
> The problem at hand pertains to the "Math" sheet, cells D2-E10. Once I
can
> get those cells to function correctly, I simply need to get that
information
> into it's proper place on the "Score Summary" sheet and the long project
is
> complete.
>
> There are four levels of tests: A, D, M, and E. Example: if the test
being
> scored is a level A, I will enter "A" into cell E16 on the "Score
Summary"
> sheet. This will affect several other cells, particularly those found in
> columns B-D , which will be hidden. The Scale Score and Grade Level are
> affected by the letter in this box and calculated accordingly.
>
> The Scale Score and Grade Level is automatically calculated for Reading,
> Language, Vocabulary, and Spelling. With Mathematics, however, there are
two
> tests, and the sum of correct answers are added into one raw score (cell
> I15). However, the two math tests carry different amounts of weight, and
so
> the scale score and grade level has to be calculated from the charts
provided
> with the sheet (See "Math A", "Math D", "Math M", and "Math E".
>
> Example: if test A was taken, and the tester scored 8 correct in
Mathematics
> Computation and 17 correct for Applied Mathematics, I would need to enter
8
> into cell "Math!C2" and 17 into cell "Math!C7". I would then need the
> information from "MathA!L39" to appear in cell "Math!D12" and the
information
> from "MathA!L40" to appear in cell "Math!E12". This information can then
be
> easily moved into the correct place the Score Summary sheet.
>
> Another example, if necessary: if test D was taken, and the tester scored
20
> on Mathematics Computation and 20 on Applied Mathematics, I would need to
> enter 20 into cell C3 and 20 into cell C8 (on the "Math" sheet. I would
then
> need the information from "MathD!X45" to appear in cell "Math!D13" and the
> information from cell "MathD!X46" to appear in cell "Math!E13".
>
> Thank you so much for your assistance and ideas.
>
> Jared Price
> [email protected]
>
> P.S. If you find yourself wondering about some of the less intelligent
> design using lookup statements and such, just know that it seemed easier
at
> the time. I didn't build the four charts until later in the process.

Hi Jared,

For the A test try this in D12 on the Math sheet...

=INDEX('Math A'!$D$5:$AC$56,$C7*2+1,$C2+1)
which returned 556

and this in E12...

=INDEX('Math A'!$D$5:$AC$56,$C7*2+2,$C2+1)

which returned 8.2


Similarly for the other levels...

Level D...

=INDEX('Math D'!$D$5:$AC$56,$C8*2+1,$C3+1)
in D13 on the Math sheet, and...

=INDEX('Math D'!$D$5:$AC$56,$C8*2+2,$C3+1)
in E13 on the Math Sheet, etc for the remaining two levels M and E.


Ken Johnson