Excel grade formula

0 votes

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, 2023 in Others by Kithuzzz
• 38,000 points
507 views

1 answer to this question.

0 votes

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.,

img

answered Jan 3, 2023 by narikkadan
• 63,640 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,038 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,690 points
411 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,640 points
776 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
• 63,640 points
820 views
0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
• 63,640 points
972 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,640 points
687 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,640 points
922 views
0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 63,640 points
1,457 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,640 points
711 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,640 points
1,914 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP