I'm working on a formula to determine the final grade for a student who completed two examinations and one assignment.

I created a formula that can accomplish the work, but I've run into a snag and I'm not entirely sure why it's returning the wrong answer.

The results for the two examinations and one assignment are A, A+, and B+, respectively, and when they are averaged, should equal 13.66 and a grade of A (rounded upwards). However, when I click outside of the cell, a C+ appears, and I'm not sure why.

```=INDEX(L\$2:L\$16,MATCH(SUMPRODUCT((E8:G8=L\$2:L\$16)*(M\$2:M\$16))/COUNTA(E8:G8), M\$2:M\$16,-1))
```
Jan 3 in Others 54 views

## 1 answer to this question.

Since you said it was just for three scores, the quickest method is to utilize the average of three different lookups, like in:

`=AVERAGE(VLOOKUP(E6,gradevalues,2,FALSE),VLOOKUP(F6,gradevalues,2,FALSE),VLOOKUP(G6,gradevalues,2,FALSE))`

...where grade values is a named range representing the list of letter grades-to-scores.

eg., • 53,520 points

## MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

## How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE