Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
showing lookup result with multiple variables
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" wrote in message ... 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 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
showing lookup result with multiple variables
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency to Text | Excel Worksheet Functions | |||
VBA | Excel Worksheet Functions | |||
Convert number into words | Excel Discussion (Misc queries) | |||
Amount or Numbers in Words | New Users to Excel | |||
Spellnumber | Excel Worksheet Functions |