Having excel choose a specific cell in a table based on two columns

brianu

New Member
Joined
Jul 9, 2009
Messages
19
I am trying to create a spreadsheet to calculate GPA for a high school. If I have the type of course and a grade, example, honors with a grade of a A. What formula can I use to pinpoint where this falls in a grid.
If you need any additional information please let me know. I would like the quality points for this particular scenario to be displayed in K41. In this case the quality points that should be displayed is 6.0. Thank you. Here is my data:
Course DifficultyGradeQuality Points Earned
Final AvgLetter GradeQuality Points Scale forHonorsA
APHonorsAcademicElectiveUnweighted GPAAPB
100-98A+7.36.35.34.34.3AcademicA+
97-94A; O; P7.06.05.04.04.0ElectiveB+
93-92A-6.75.74.73.73.7
91-89B+6.35.34.33.33.3
88-85B; S6.05.04.03.03.0
84-83B-5.74.73.72.72.7
82-80C+5.34.33.32.32.3
79-76C5.04.03.02.02.0
75-74C-4.73.72.71.71.7
73-71D+4.33.32.31.31.3
70-67D4.03.02.01.01.0
66-65D-3.72.71.70.70.7
64-50F0.00.00.00.00.0
49-0X; U0.00.00.00.00.0

<colgroup><col span="4"><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi brianu, try this.

ABCDEFGHIJK
40Course DifficultyGradeQuality Points Earned
41Final AvgLetter GradeQuality PointsScale forHonorsA6.0
42APHonorsAcademicElectiveUnweighted GPAAPB6.0
43100-98A+7.36.35.34.34.3AcademicA+5.3
4497-94A; O; P76544ElectiveB+3.3
4593-92A-6.75.74.73.73.7
4691-89B+6.35.34.33.33.3
4788-85B; S65433
4884-83B-5.74.73.72.72.7
4982-80C+5.34.33.32.32.3
5079-76C54322
5175-74C-4.73.72.71.71.7
5273-71D+4.33.32.31.31.3
5370-67D43211
5466-65D-3.72.71.70.70.7
5564-50F00000
5649-0X; U00000

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
K41{=INDEX($C$43:$G$56,IFERROR(MATCH($J41,$B$43:$B$56,0),SUM((ROW($B$43:$B$56)-MIN(ROW($B$43:$B$56))+1)*IF(ISNUMBER(SEARCH($J41,$B$43:$B$56)),1)*IF(ISNUMBER(SEARCH(";",$B$43:$B$56)),1))),MATCH($I41,$C$42:$G$42,0))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!


<tbody>
</tbody>
 

brianu

New Member
Joined
Jul 9, 2009
Messages
19
Exceldevs,
Thank you so much! Worked like a charm. I am quite impressed. Have a great day.

Brian
 

brianu

New Member
Joined
Jul 9, 2009
Messages
19
Question for you exceldevs, or anyone that comes across this. The data I import will not have the course difficulty level instead it will have something like AP English or Physics Hon is there something I could do to the formula you gave me before for it to search for AP or Hon in order to determine the difficulty of the course and which quality points column to use. Thank you again as you have been tremendously helpful.

Brian
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi Brian,

The formula was constructed in a flexible/extensible way that as long as you have matches between column I and C42:G42 it shall work.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top